Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSIS: use 2 different connections managers in one datasource Expand / Collapse
Author
Message
Posted Tuesday, July 09, 2013 3:11 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:42 AM
Points: 869, Visits: 349
Hi,

I'm using SSIS to fill a datawarehouse. Until now I used to truncate the tables and refill them every night. Since this job is taking too long, I don't want to empty the tables anymore but only add new records. To do that, I have a column in a table that contains the LastUsedNumber. This is not a primary key column.
Now that I have the lastusednumber I need to select the new records.

The script will look like this:
SELECT * from table1
WHERE recordnumber > LastUsedNumber

Nothing strange until now.
The problem that I have is that the data source of the select is a table of an AS400 system (DB2) and that the data source of the LastUsedNumber is SQL Server .
Since I want to write a SQL Command as data access mode, how can I use 2 different connection sources in one SSIS Data Flow Source because I can define only one connection manager in the source.

I would appreciate it if someone has a solution for me or an way to get around this.

Many thanks.
Vera Van Boxel
Post #1471488
Posted Tuesday, July 09, 2013 6:37 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, April 07, 2014 11:04 AM
Points: 57, Visits: 613
Workaround:
Fetch the LastUsedNumber in your control flow, store it in a variable, and either:
A) pass it into your data flow source as a parameter
B) Create the SQL statement as an expression with a second variable
Post #1471583
Posted Tuesday, July 09, 2013 3:05 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 7:29 AM
Points: 12,206, Visits: 9,168
A little more detail.

1. Use an Execute SQL Task to retrieve the LastUsedNumber and store the result in a variable (let's call it @LastUsedNumber :). See this article on how to do that: Execute SQL Task.

2. Create a string variable called @SQLStatement. Put the EvaluateAsExpression property to true and enter the following expression for the value property:

"SELECT * from table1 WHERE recordnumber > " + (DT_STR,10,1252) @[User::LastUsedNumber]

3. In your OLE DB Source, choose the SQL command from variable option and pick the @SQLStatement variable.




How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?

Member of LinkedIn. My blog at LessThanDot.

MCSA SQL Server 2012 - MCSE Business Intelligence
Post #1471870
Posted Tuesday, July 09, 2013 11:39 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:42 AM
Points: 869, Visits: 349
Thanks, I'll give it a try and will keep you uptodate on how it goes.
Post #1471951
Posted Wednesday, July 10, 2013 12:45 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:42 AM
Points: 869, Visits: 349
The problem is that I cannot use an OLE DB Source, it's a connection to DB2 on AS400 system, I need to use ADO.NET source and there is no option to use SQL statement with variables.
Post #1471971
Posted Wednesday, July 10, 2013 3:43 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 1:42 AM
Points: 869, Visits: 349
I used a workaround with a linked server and stored procedure.
Thank you for taking time to watch into this.
Post #1472030
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse