Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS: use 2 different connections managers in one datasource


SSIS: use 2 different connections managers in one datasource

Author
Message
Vera-428803
Vera-428803
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1112 Visits: 443
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
larskandersen
larskandersen
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 1022
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
Koen Verbeeck
Koen Verbeeck
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16467 Visits: 13207
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 Smile. 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 SQLKover.

MCSA SQL Server 2012 - MCSE Business Intelligence
Vera-428803
Vera-428803
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1112 Visits: 443
Thanks, I'll give it a try and will keep you uptodate on how it goes.
Vera-428803
Vera-428803
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1112 Visits: 443
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.
Vera-428803
Vera-428803
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1112 Visits: 443
I used a workaround with a linked server and stored procedure.
Thank you for taking time to watch into this.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search