Hi all,
I know that the File connection can be used in the File system task and Flatfile connection can be used in Flat File source or destination, but I don't know which Task or Source/Destination can use the Multiple File connection and Multiple Flatfile connection. Thanks
you have to have as many connection as the file
|||The tasks/components that use the single version of connections should be able to use the "multi" ones if you run them in loops.
Thanks.
Hi, I have a unique problem that I am currently unable to figure out. I need to populate a where clause in a SQL statement that has multiple values, however those values always change because they are in another table. The end result that I want to end up with is a list of subs that belong to all of the UCI's that were selected for a particular bid number.
I have the following tables
tblBid with two columns. Bid_ID, and Uci_ID . This table contains multlple rows with the same Bid_ID but the Uci_ID is never the same for the current Bid_id. For example. If I had a Bid_ID of 123, I might have mutliple records listing
bid_id Uci_id
123 1000
123 2000
123 1050
tblSubs_By_Uci that has two columns. Sub_ID, and Uci_ID . This talbe contains a list of Uci_id's that Subs belong to. So I will have only multiple Sub_id and mulitple UCI_ID's because a sub can belong to mulitple Uci_ID's.
Uci_ID Sub_ID
1000 456
1000 2345
2000 456
1050 2345
2000 2345
This is the statement I am using to return the Uci's from the Bid table with bid_id of 123. For example. when I run the following sql statement, it will list all of the UCI's for bid_id 123. SELECT Uci_ID from tblBid where Bid_ID = 123 . That produces a list of UCI's. Now I want to find each sub that belongs to each of the UCI's using that list.
SELECT Sub_ID from tblSubs_BY_UCI where Uci_ID = (SELECT Uci_ID from tblBid WHERE Bid_ID = 123) . I of course get an error from sql saying that I can not pass multiple values to the Where clause.
Can someone please help point me in the right direction. I have been searching on the net for days trying to figure this out. I am open to any suggestions.
This is how you have to do it
SELECT Sub_ID from tblSubs_BY_UCI where Uci_ID IN (SELECT Uci_ID from tblBid WHERE Bid_ID = 123)
There are 2 ways.
SELECT Sub_IDfrom tblSubs_BY_UCIwhere Uci_IDin (SELECT Uci_IDfrom tblBidWHERE Bid_ID = 123 )-- or ----SELECT tb.Uci_ID , ts.Sub_IDfrom tblSubs_BY_UCI ts , tblBid tbwhere ts.Uci_ID = tb.Uci_IDand tb.Bid_ID = 123Hope this will help.|||
Thanks.
By Changing the = to IN, it worked perfectly.
Hi everyone,
There is a small problem encountered while creating a package in sql
server 2005.
Actually i am using a flat file which has 820 rows and 2 columns which
are seperated by line feed(for ROW) and tab(for COLUMN).after
importing i found that ther are only 800 rows imported into the table.
Ather verifying the input file i found out that there are some null
values in the second column so there is no line feed for those
values.
Can anyone please help me how to give multiple delimiters for the same
input flat file.
SSIS parses column by column, not by row and then by column. To handle this type of issue (an improperly formatted flat file), you have to pull each row in as a single column, then parse the rows into columns yourself, checking for any error conditions.
See http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx for an example.
|||Hi,
Thank you for your response.But i still encounter a problem.
It is associated with READ ONLY, READ WRITE.These are the errors i am getting.Any solution for those things
|||I need a bit more information to help you with that. What are the actual error messages, and where are they showing up?
|||Hi,
Actually there a 2 errors and 4 warnings that are showing up when i go to the design script page itself. Before writing any code itself they are showing up. I will write you the errors. They are,
ERRORS:
1: Error 5 'Public ReadOnly Property CLASSCODE() As String' and 'Public WriteOnly Property CLASSCODE() As String' cannot overload each other because they differ only by 'ReadOnly' or 'WriteOnly'.
2: Error 6 'Public ReadOnly Property CLASSCODE_IsNull() As Boolean' and 'Public WriteOnly Property CLASSCODE_IsNull() As Boolean' cannot overload each other because they differ only by 'ReadOnly' or 'WriteOnly'.
WARNINGS:
1: Warning 1 The dependency 'EnvDTE' could not be found.
2: Warning 2 The dependency 'Microsoft.SqlServer.VSAHosting' could not be found.
3: Warning 3 The dependency 'Microsoft.SqlServer.DtsMsg' could not be found.
4: Warning 4 The dependency 'Microsoft.SqlServer.VSAHostingDT' could not be found.
Thanks in advance
|||Make sure you are not using the same name in the Input columns and the Output columns. Output column names must be different that the input column names. You should be getting a warning about this in the data flow designer as well.On the warnings, make sure your script project has references to the assemblies.
|||Ya i have rectified the problem and it is working fine. One more small modification that need to be done to my output table.
As said earlier there are 2 columns in my input and in the second column there are null values. But after all the scripting i find in the output table the places where i had null values,both the colunm is showing NULL VALUES. Any procedure that needs to be followed to rectify these NULL VALUES in the first column.
|||Sounds like a problem in the script - could you share what you are using?Hi everyone,
There is a small problem encountered while creating a package in sql
server 2005.
Actually i am using a flat file which has 820 rows and 2 columns which
are seperated by line feed(for ROW) and tab(for COLUMN).after
importing i found that ther are only 800 rows imported into the table.
Ather verifying the input file i found out that there are some null
values in the second column so there is no line feed for those
values.
Can anyone please help me how to give multiple delimiters for the same
input flat file.
SSIS parses column by column, not by row and then by column. To handle this type of issue (an improperly formatted flat file), you have to pull each row in as a single column, then parse the rows into columns yourself, checking for any error conditions.
See http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx for an example.
|||Hi,
Thank you for your response.But i still encounter a problem.
It is associated with READ ONLY, READ WRITE.These are the errors i am getting.Any solution for those things
|||I need a bit more information to help you with that. What are the actual error messages, and where are they showing up?
|||Hi,
Actually there a 2 errors and 4 warnings that are showing up when i go to the design script page itself. Before writing any code itself they are showing up. I will write you the errors. They are,
ERRORS:
1: Error 5 'Public ReadOnly Property CLASSCODE() As String' and 'Public WriteOnly Property CLASSCODE() As String' cannot overload each other because they differ only by 'ReadOnly' or 'WriteOnly'.
2: Error 6 'Public ReadOnly Property CLASSCODE_IsNull() As Boolean' and 'Public WriteOnly Property CLASSCODE_IsNull() As Boolean' cannot overload each other because they differ only by 'ReadOnly' or 'WriteOnly'.
WARNINGS:
1: Warning 1 The dependency 'EnvDTE' could not be found.
2: Warning 2 The dependency 'Microsoft.SqlServer.VSAHosting' could not be found.
3: Warning 3 The dependency 'Microsoft.SqlServer.DtsMsg' could not be found.
4: Warning 4 The dependency 'Microsoft.SqlServer.VSAHostingDT' could not be found.
Thanks in advance
|||Make sure you are not using the same name in the Input columns and the Output columns. Output column names must be different that the input column names. You should be getting a warning about this in the data flow designer as well.On the warnings, make sure your script project has references to the assemblies.
|||Ya i have rectified the problem and it is working fine. One more small modification that need to be done to my output table.
As said earlier there are 2 columns in my input and in the second column there are null values. But after all the scripting i find in the output table the places where i had null values,both the colunm is showing NULL VALUES. Any procedure that needs to be followed to rectify these NULL VALUES in the first column.
|||Sounds like a problem in the script - could you share what you are using?Hi everyone,
There is a small problem encountered while creating a package in sql
server 2005.
Actually i am using a flat file which has 820 rows and 2 columns which
are seperated by line feed(for ROW) and tab(for COLUMN).after
importing i found that ther are only 800 rows imported into the table.
Ather verifying the input file i found out that there are some null
values in the second column so there is no line feed for those
values.
Can anyone please help me how to give multiple delimiters for the same
input flat file.
SSIS parses column by column, not by row and then by column. To handle this type of issue (an improperly formatted flat file), you have to pull each row in as a single column, then parse the rows into columns yourself, checking for any error conditions.
See http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx for an example.
|||Hi,
Thank you for your response.But i still encounter a problem.
It is associated with READ ONLY, READ WRITE.These are the errors i am getting.Any solution for those things
|||I need a bit more information to help you with that. What are the actual error messages, and where are they showing up?
|||Hi,
Actually there a 2 errors and 4 warnings that are showing up when i go to the design script page itself. Before writing any code itself they are showing up. I will write you the errors. They are,
ERRORS:
1: Error 5 'Public ReadOnly Property CLASSCODE() As String' and 'Public WriteOnly Property CLASSCODE() As String' cannot overload each other because they differ only by 'ReadOnly' or 'WriteOnly'.
2: Error 6 'Public ReadOnly Property CLASSCODE_IsNull() As Boolean' and 'Public WriteOnly Property CLASSCODE_IsNull() As Boolean' cannot overload each other because they differ only by 'ReadOnly' or 'WriteOnly'.
WARNINGS:
1: Warning 1 The dependency 'EnvDTE' could not be found.
2: Warning 2 The dependency 'Microsoft.SqlServer.VSAHosting' could not be found.
3: Warning 3 The dependency 'Microsoft.SqlServer.DtsMsg' could not be found.
4: Warning 4 The dependency 'Microsoft.SqlServer.VSAHostingDT' could not be found.
Thanks in advance
|||Make sure you are not using the same name in the Input columns and the Output columns. Output column names must be different that the input column names. You should be getting a warning about this in the data flow designer as well.On the warnings, make sure your script project has references to the assemblies.
|||Ya i have rectified the problem and it is working fine. One more small modification that need to be done to my output table.
As said earlier there are 2 columns in my input and in the second column there are null values. But after all the scripting i find in the output table the places where i had null values,both the colunm is showing NULL VALUES. Any procedure that needs to be followed to rectify these NULL VALUES in the first column.
|||Sounds like a problem in the script - could you share what you are using?Hi everyone,
There is a small problem encountered while creating a package in sql
server 2005.
Actually i am using a flat file which has 820 rows and 2 columns which
are seperated by line feed(for ROW) and tab(for COLUMN).after
importing i found that ther are only 800 rows imported into the table.
Ather verifying the input file i found out that there are some null
values in the second column so there is no line feed for those
values.
Can anyone please help me how to give multiple delimiters for the same
input flat file.
SSIS parses column by column, not by row and then by column. To handle this type of issue (an improperly formatted flat file), you have to pull each row in as a single column, then parse the rows into columns yourself, checking for any error conditions.
See http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx for an example.
|||Hi,
Thank you for your response.But i still encounter a problem.
It is associated with READ ONLY, READ WRITE.These are the errors i am getting.Any solution for those things
|||I need a bit more information to help you with that. What are the actual error messages, and where are they showing up?
|||Hi,
Actually there a 2 errors and 4 warnings that are showing up when i go to the design script page itself. Before writing any code itself they are showing up. I will write you the errors. They are,
ERRORS:
1: Error 5 'Public ReadOnly Property CLASSCODE() As String' and 'Public WriteOnly Property CLASSCODE() As String' cannot overload each other because they differ only by 'ReadOnly' or 'WriteOnly'.
2: Error 6 'Public ReadOnly Property CLASSCODE_IsNull() As Boolean' and 'Public WriteOnly Property CLASSCODE_IsNull() As Boolean' cannot overload each other because they differ only by 'ReadOnly' or 'WriteOnly'.
WARNINGS:
1: Warning 1 The dependency 'EnvDTE' could not be found.
2: Warning 2 The dependency 'Microsoft.SqlServer.VSAHosting' could not be found.
3: Warning 3 The dependency 'Microsoft.SqlServer.DtsMsg' could not be found.
4: Warning 4 The dependency 'Microsoft.SqlServer.VSAHostingDT' could not be found.
Thanks in advance
|||Make sure you are not using the same name in the Input columns and the Output columns. Output column names must be different that the input column names. You should be getting a warning about this in the data flow designer as well.On the warnings, make sure your script project has references to the assemblies.
|||Ya i have rectified the problem and it is working fine. One more small modification that need to be done to my output table.
As said earlier there are 2 columns in my input and in the second column there are null values. But after all the scripting i find in the output table the places where i had null values,both the colunm is showing NULL VALUES. Any procedure that needs to be followed to rectify these NULL VALUES in the first column.
|||Sounds like a problem in the script - could you share what you are using?Hi everyone,
There is a small problem encountered while creating a package in sql
server 2005.
Actually i am using a flat file which has 820 rows and 2 columns which
are seperated by line feed(for ROW) and tab(for COLUMN).after
importing i found that ther are only 800 rows imported into the table.
Ather verifying the input file i found out that there are some null
values in the second column so there is no line feed for those
values.
Can anyone please help me how to give multiple delimiters for the same
input flat file.
SSIS parses column by column, not by row and then by column. To handle this type of issue (an improperly formatted flat file), you have to pull each row in as a single column, then parse the rows into columns yourself, checking for any error conditions.
See http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx for an example.
|||Hi,
Thank you for your response.But i still encounter a problem.
It is associated with READ ONLY, READ WRITE.These are the errors i am getting.Any solution for those things
|||I need a bit more information to help you with that. What are the actual error messages, and where are they showing up?
|||Hi,
Actually there a 2 errors and 4 warnings that are showing up when i go to the design script page itself. Before writing any code itself they are showing up. I will write you the errors. They are,
ERRORS:
1: Error 5 'Public ReadOnly Property CLASSCODE() As String' and 'Public WriteOnly Property CLASSCODE() As String' cannot overload each other because they differ only by 'ReadOnly' or 'WriteOnly'.
2: Error 6 'Public ReadOnly Property CLASSCODE_IsNull() As Boolean' and 'Public WriteOnly Property CLASSCODE_IsNull() As Boolean' cannot overload each other because they differ only by 'ReadOnly' or 'WriteOnly'.
WARNINGS:
1: Warning 1 The dependency 'EnvDTE' could not be found.
2: Warning 2 The dependency 'Microsoft.SqlServer.VSAHosting' could not be found.
3: Warning 3 The dependency 'Microsoft.SqlServer.DtsMsg' could not be found.
4: Warning 4 The dependency 'Microsoft.SqlServer.VSAHostingDT' could not be found.
Thanks in advance
|||Make sure you are not using the same name in the Input columns and the Output columns. Output column names must be different that the input column names. You should be getting a warning about this in the data flow designer as well.On the warnings, make sure your script project has references to the assemblies.
|||Ya i have rectified the problem and it is working fine. One more small modification that need to be done to my output table.
As said earlier there are 2 columns in my input and in the second column there are null values. But after all the scripting i find in the output table the places where i had null values,both the colunm is showing NULL VALUES. Any procedure that needs to be followed to rectify these NULL VALUES in the first column.
|||Sounds like a problem in the script - could you share what you are using?There is a small problem encountered while creating a package in sql
server 2005.
Actually i am using a flat file which has 820 rows and 2 columns which
are seperated by line feed(for ROW) and tab(for COLUMN).after
importing i found that ther are only 800 rows imported into the table.
Ather verifying the input file i found out that there are some null
values in the second column so there is no line feed for those
values.
Can anyone please help me how to give multiple delimiters for the same
input flat file.
Quote:
Originally Posted by gopiganguly
Hi everyone,
There is a small problem encountered while creating a package in sql
server 2005.
Actually i am using a flat file which has 820 rows and 2 columns which
are seperated by line feed(for ROW) and tab(for COLUMN).after
importing i found that ther are only 800 rows imported into the table.
Ather verifying the input file i found out that there are some null
values in the second column so there is no line feed for those
values.
Can anyone please help me how to give multiple delimiters for the same
input flat file.
If you saw it in a text editor, it sounds like a different linefeed is being used (carriage return + linefeed combination opposed to just linefeed).
Otherwise, replace nulls with linefeed characters. You can use the SQL REPLACE function. Or, open the file with filesystemobject.textstream, readall(), then do a replace on the returned stream.
Hi everyone,
How to change dynamically the connection properties for a DTSX?
Imagine that you launch a SSIS and under a several rules the same connection is reused with four or five different databases.
I know that I can attach a configuration file or more than one but how to tell to SSIS which use in every moment?
Thanks in advance and regards,
enric vives wrote:
Hi everyone,
How to change dynamically the connection properties for a DTSX?
Imagine that you launch a SSIS and under a several rules the same connection is reused with four or five different databases.
I know that I can attach a configuration file or more than one but how to tell to SSIS which use in every moment?
Thanks in advance and regards,
Do you mean you need to execute the same package multiple times but assigning different connection strings to connection managers on every execution?
|||Assign the InitialCatalog property of your connection object to a variable using expressions, and then make a Script task that sets the variable property depending on your condition.I think it'd be better if you could just set one username/password for all databases so that you'll only need one config file for them all. This is only a problem if the passwords are all different (which I suspect them to be).|||Thanks for that.
Hello,
We have a cube in AS2005, and it is going to be used by multiple users. The users will all have the ability to process the cube by clicking on a button.
Is there a way to to use AMO to check whether the cube is currently being processed?
Thank you very much,
Hsiao-I
This is pretty easy. Take a look at return value for the State property of the major objects.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
