March 29, 2012 at 5:43 pm
Hi..I have a DFT in which the following script has been used as a source:
SELECT * FROM TBL_A WHERE CODES IN('1','2','3','4')
To get rid of hard coding the code values in the above query, I have created a table in sql server, which holds the codes. The query run against a different server and the code table is stored in a different server. So, now how can I use the code table in the source
e.g.
1.I cannot use
SELECT * FROM TBL_A WHERE CODES IN(select * from codes)
as both the tables are in different servers.
2. I cannot use merge joins as the data in tbl_A is huge..which causes performance issues.
One workaround I used is an object variable and using execute sql task, stored the above values from code table in the variable. Now, in source, I am using script
SELECT * FROM TBL_A WHERE CODES IN(?)
and passed the object variable to the above query.
But, when I run the package, I get an error..
[diabetics patients 1 [1]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E21.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E21 Description: "Invalid character value for cast specification".
The data type for the codes in the object variable and codes in tbl_A is varchar(10), so all the datatypes are same as well.
Can somebody tell me how to get rid of this error or any other way to get rid of hard coding?
Thank you!!
March 30, 2012 at 1:44 am
You cannot use the object variable directly like that.
I would read from the source with the following query:
SELECT col1, col2, ..., coln FROM TBL_A
I have obviously replaced the * with the columns as best practice dictates.
Then I would link the source to a lookup component with full caching that has the following query:
select code from codes
(again, I replaced the *, because my eyes started to bleed)
In the general pane, configure the lookup component to route non-matching rows to the no match output. However, map only the match output to your destination. This way you'll effectively discard the no matching rows.
You'll read all the data from table A, but because you use non-blocking synchronous components this will go pretty fast. You can speed up things by enlarging the network package size on the OLE DB connection managers and by enlarging the buffers in the SSIS dataflow.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 30, 2012 at 2:20 am
Or create a linked server on the TBL_A server to the server with the codes table. Then create a view on the TBL_A server which returns the required data (join to the codes table as required). Then use the view in SSIS as your data source.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 30, 2012 at 2:26 am
Phil Parkin (3/30/2012)
Or create a linked server on the TBL_A server to the server with the codes table. Then create a view on the TBL_A server which returns the required data (join to the codes table as required). Then use the view in SSIS as your data source.
I had that in my head, but usually when I suggest linked servers I get the reply:
"ooooh nooos, not linked server. They're evil!!! Administrators will kill me! Apocalypse is near!"
Something like that.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 30, 2012 at 2:30 am
Koen Verbeeck (3/30/2012)
Phil Parkin (3/30/2012)
Or create a linked server on the TBL_A server to the server with the codes table. Then create a view on the TBL_A server which returns the required data (join to the codes table as required). Then use the view in SSIS as your data source.I had that in my head, but usually when I suggest linked servers I get the reply:
"ooooh nooos, not linked server. They're evil!!! Administrators will kill me! Apocalypse is near!"
Something like that.
Haha, I'm ready for that 🙂 And I am the administrator here 😎
Your solution is purer SSIS and I like it - mine's just another way of skinning that cat.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
March 30, 2012 at 3:42 pm
Thank you so much guys for the wonderful replies.. 🙂
Koen Verbeeck (3/30/2012)
You cannot use the object variable directly like that.I would read from the source with the following query:
SELECT col1, col2, ..., coln FROM TBL_A
I have obviously replaced the * with the columns as best practice dictates.
Then I would link the source to a lookup component with full caching that has the following query:
select code from codes
(again, I replaced the *, because my eyes started to bleed)
In the general pane, configure the lookup component to route non-matching rows to the no match output. However, map only the match output to your destination. This way you'll effectively discard the no matching rows.
You'll read all the data from table A, but because you use non-blocking synchronous components this will go pretty fast. You can speed up things by enlarging the network package size on the OLE DB connection managers and by enlarging the buffers in the SSIS dataflow.
our method is fantastic..but as the data in the source table is huge..I am unable to use this approach as the package is taking a loooooong time in pre-execute stage.. 🙁
March 30, 2012 at 3:47 pm
I am have got the contents of the objct variable in a string variable..Now I am using a script component as source so that I can pass this string variable to the SELECT script..
Below is my code:
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Data.SqlClient;
using Microsoft.SqlServer.Dts.Runtime;
using System.Math;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
IDTSConnectionManager100 connMgr;
SqlConnection sqlConn;
SqlDataReader sqlReader;
public override void AcquireConnections(object Transaction)
{
connMgr = this.Connections.
sqlConn = (SqlConnection)connMgr.AcquireConnection("SQL200\\TEST200.OperationalReporting");
}
public override void PreExecute()
{
//base.PreExecute();
/*
Add your code here for preprocessing or remove if not needed
*/
SqlCommand cmd = new SqlCommand("SELECT DISTINCT TAG FROM UCSFDiabetes.CODES", sqlConn);
sqlReader = cmd.ExecuteReader();
}
public override void PostExecute()
{
//base.PostExecute();
/*
Add your code here for postprocessing or remove if not needed
You can set read/write variables here, for example:
Variables.MyIntVar = 100
*/
//base.PostExecute();
sqlReader.Close();
}
public override void CreateNewOutputRows()
{
/*
Add rows by calling the AddRow method on the member variable named "<Output Name>Buffer".
For example, call MyOutputBuffer.AddRow() if your output was named "MyOutput".
*/
while (sqlReader.Read())
{
{
Output0Buffer.AddRow();
Output0Buffer.TAG = sqlReader.GetInt32(0);
}
}
public override void ReleaseConnections()
{
connMgr.ReleaseConnection(sqlConn);
}
In this code..I am getting two errors:
1) Connections does not contain a definition for connection and no extension methods..blah..blah
2) Output0buffer does not contain a definition for 'TAG' 🙁
any ideas to resolve these issues?
I am trying to follow the code given here:
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply