| Author |
Topic |
|
nelsont
Starting Member
25 Posts |
 Posted - 2012-01-27 : 23:23:05
|
| Hi all,I have a simple join in a SQL task in an SSIS package.select a.col1, b.col2 into tableCfrom tableA a left join tableB b on a.id = b.idMy result should have col1 filled in for every row in tableC but mostly nulls in col2.I am using the expression builder to create the SQL statement because the tables are in two different databases - I have a variable set for each database and I am defining them in a config file.Anyway, when I run the SSIS package I get col1 filled but col2 is all nulls.If I click the "verify expression" button at the bottom of the expression builder and cut / past the result of that into SSMS I get the expected result: col1 filled and some of col2 filled.I have also tried a workaround by selecting col1, id and a blank field from tableA into a table (lets call it tableC) and then doing another commandUpdate tableC set blankfield = b.col2from tableC a, tableB bwhere a.id = b.idbut the same thing happens. When I run that in SSIS col2 is all nulls but when I run the statement in SSMS the update statement works fine.There are quite a few joins very similar to this in the SSIS package and they all work. It is just this one that is giving me trouble. I have no idea where to go from here and this is a difficult problem to search for. Any help would be greatly appreciated. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-28 : 17:44:57
|
| why are you using table using select ...into syntax inside ssis? do you know what happens if you're trying to run package more than once in this case?ALso whats the data type of col2 in source table? is ssis taking same data type for it?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
nelsont
Starting Member
25 Posts |
Posted - 2012-01-29 : 12:02:32
|
| Yes I know. Before this query is if exists (drop table). col2 is int in both the source and destination tables. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-29 : 14:19:02
|
| nope..i'm asking whats the datatype of col2 in source and destination?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
nelsont
Starting Member
25 Posts |
Posted - 2012-01-29 : 17:13:20
|
| I said int. It is INT in the source and INT in the destination. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-29 : 18:23:29
|
| ok. do you've matching records coming from tableB in the query? else left join will return only NULL values for col2------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
nelsont
Starting Member
25 Posts |
Posted - 2012-01-29 : 21:26:14
|
| Yes I do. Please take a look at my original post again. The problem is not the query. The problem is that it won't work in my package but it will in SSMS. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-29 : 21:40:36
|
| is ssis having any additional lookup steps prior to this? also check your connectionstrings and make sure its pointing to correct database correct table.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
nelsont
Starting Member
25 Posts |
Posted - 2012-01-30 : 18:32:36
|
| I'm not sure what you mean about sql having any additional lookup steps prior to this. If you mean do I create tableA and tableB in the ETL package then no, they are existing in my database already. The connection string is pointing to the correct database. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-30 : 19:46:23
|
| nope. i'm asking whether you are using table values to relate to some other table to get related field values------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
nelsont
Starting Member
25 Posts |
Posted - 2012-02-01 : 11:43:30
|
| I still don't understand what you are asking. Do you mean am I doing the same thing as this somewhere else in the package? Relating multiple tables and doing joins and updates? Yes I am doing them all over the place. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-01 : 12:18:41
|
| nope...can you explain the full work flow of package? sequence of tasks and what they does briefly?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
nelsont
Starting Member
25 Posts |
Posted - 2012-02-17 : 11:47:48
|
| I thought I would post here since I found a resolution to the problem. The problem with my post is that with the information I gave there is no way anyone could have helped me. The problem was that I was using "go" as a batch separator in the expression builder. The SQL query I originally posted was the second one of two in the same execute sql task. I guess since I used "go" instead of a semi colon the first query was not finished when the second one executed and I got unexpected results.After changing the "go" to a semicolon the query runs fine.P.S. that's also why when I pasted the query into SSMS and ran it everything worked as expected - of course "go" is recognized as the batch separator in SSMS. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-17 : 13:59:16
|
quote:
Originally posted by nelsont
I thought I would post here since I found a resolution to the problem. The problem with my post is that with the information I gave there is no way anyone could have helped me. The problem was that I was using "go" as a batch separator in the expression builder. The SQL query I originally posted was the second one of two in the same execute sql task. I guess since I used "go" instead of a semi colon the first query was not finished when the second one executed and I got unexpected results.After changing the "go" to a semicolon the query runs fine.P.S. that's also why when I pasted the query into SSMS and ran it everything worked as expected - of course "go" is recognized as the batch separator in SSMS.
ok...that explains it------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
SQL works in Management Studio but not in SSIS