Changing the value of a variable

I'm looking for all possible ways to change the value of a variable.

I know this ways:

- edit direct

- configuration file

- scripting task

- scripting component

It is possible to change the value with other components?

Can we assign the output of Execute SQL Task to a variable?|||

It is unclear whether you are looking for tasks/components that change the value of a variable or whether you are looking for ways that it can be done. Any task/component can theoretically change the value of any variable in scope (excluding system and read only variables of course). Additionally expressions can change the value of a variable and as you mention configurations can as well. Also the ForEach loop via its variable mappings will change variable values.

Off the top of my head the sql task, row count component, execute dts package task, and recordset destination change variable, but I wouldn't say this list is exhuastive.

Matt

|||

@. Matt:

I'm looking for a general overview. Perhaps there is an overview in BO or in a blog or anywhere else.

@.Nitesh:

try this:

1. Define a variable with the datatyp = Object

2. Set ResultSet to Single Row

3. At the ResultSet-Tab add a line and configure your variable to a Outputname

4. Write a SQL-Statement : select max(abst) as output from art

Loom

|||

There is a walkthrough of the Exec SQL Task here
(http://www.sqlis.com/default.aspx?58)

Another variable setting solution is the ExecValueVariable. Several tasks support this, which is a way to specify a variable into which the task puts some form of execution result data, for example the Transfer SQL Server Objects Task puts the number of objects transferred. These can be usefull, although MS's own implementation seems to be a bit sparse, but use them when you can.

sql

Changing global variable based on day of the week

I'm new to SSIS so please be gentle...

I'm creating a package that needs to go to an FTP site (FTP Task), download a file, unzip it and then process a series of table loads for the 12 text files that will be unzipped. My problem is that the zip file is a date (yyyymmdd.zip) which is normally the previous day of execution EXCEPT on Mondays when it would be the previous Friday's date. My thought is that IF (magic question) I could determine the day of the week in the SSIS package, I know that Tuesday-Friday is just a formatting exercise of getdate()-1 and Monday would be getdate()-3 but I can't seem to find a way (function?) that will allow me to determine the day of the week?

Thanks in advance!

Is that the only file on the site? If so it is easier to just download it using a wildcard *.zip. Then use a for each loop to process each file in the folder which will pick up the filename for you.

Even if it's not it's probably better to check all files on the site and process those which you haven't processed before.

What happens on bank holidays.

|||

Thanks for replying. Unfortunately it is not the only file (they keep them for archival purposes so there are hundreds). Is there an easy way to track which files I've processed (flag/switch) or are you suggesting a directory comparison or storing file names in a table somehow (not sure how to compare?)?

If there is no file, it just emails a failure notice and the process would be run manually as it is now (not the most efficient way but we can get to bells and whistles later).

You can disregard this question. I actually found the solution in a posting from "killerless" on 8/30/2006. He/she actually did pretty much the exact expression I needed to calculate the day. I love forums! :-)

|||

I would use a ForEachLoop conatiner with an expression in FileSpec property. The FileSpec property overrides the 'files' entry in the conatiner GUI. The expression should have the logic to get the right file name based on the system or execution date. I think sothing like this should work:

Datepart("dw", @.[User::CurrentDate] ) == 2 ?
(DT_WSTR,4)DATEPART("YYYY", @.[User::CurrentDate]) + RIGHT("0" + (DT_WSTR,2)DATEPART("MM", @.[User::CurrentDate]),2) + RIGHT("0" + (DT_WSTR,20) DAY(DATEADD("dd", -3, @.[User::CurrentDate])),2) + ".zip"
:
(DT_WSTR,4)DATEPART("YYYY", @.[User::CurrentDate]) + RIGHT("0" + (DT_WSTR,2)DATEPART("MM", @.[User::CurrentDate]),2) + RIGHT("0" + (DT_WSTR,20) DAY(DATEADD("dd", -1, @.[User::CurrentDate])),2) + ".zip"

It says someting like: if its friday; then substarct 3 days otherwise substract 1.

Put all the logic inside of the container. Let me know if you want the sample package.

|||It looks like my answer got too late |||Sorry! I do appreciate the effort!

Changing flat file connection file name property

Hi,

I have a task to traverse a folder of CSV files of same format and then populate into one sql server table.

Is there a way where I can change the source CSV file name runtime using FOR EACH loop container for flat file connection manager ?

any help would be much appriciated.

Thanks,

Furrukh Baig

Yes. By using package expressions. There is an example in the SSIS tutorials that I think does exactly what you want to do assuming all files are in the same directory.|||Thanks mate... you are star ...|||

I'm also trying to do the same thing, except that each folder has a number of different csv files. Would appreciate if someone could give me a link of the sample (as mentioned in the previous post) and whether it's better to have different flat file source for each type of csv file format or should I just use 1 flat file source.

I've tried using 1 flat file source but the number of columns are different for each files and the number of columns in the flat file source isn't dynamic (I've to prespecify it first).

|||I'm assuming you're talking about the foreach loop container in the tutorial (ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/sqltut9/html/88a973cc-0f23-4ecf-adb6-5b06279c2df6.htm) Can you use a similar concept to loop thru xml files? I have an xml source, 2 sorts and a merge-join. Whenever I change the input file, I need to open my sorts and click ok. How will that work if I need to iterate thru a directory of files?

Changing flat file connection file name property

Hi,

I have a task to traverse a folder of CSV files of same format and then populate into one sql server table.

Is there a way where I can change the source CSV file name runtime using FOR EACH loop container for flat file connection manager ?

any help would be much appriciated.

Thanks,

Furrukh Baig

Yes. By using package expressions. There is an example in the SSIS tutorials that I think does exactly what you want to do assuming all files are in the same directory.|||Thanks mate... you are star ...|||

I'm also trying to do the same thing, except that each folder has a number of different csv files. Would appreciate if someone could give me a link of the sample (as mentioned in the previous post) and whether it's better to have different flat file source for each type of csv file format or should I just use 1 flat file source.

I've tried using 1 flat file source but the number of columns are different for each files and the number of columns in the flat file source isn't dynamic (I've to prespecify it first).

|||I'm assuming you're talking about the foreach loop container in the tutorial (ms-help://MS.VSCC.v80/MS.VSIPCC.v80/MS.SQLSVR.v9.en/sqltut9/html/88a973cc-0f23-4ecf-adb6-5b06279c2df6.htm) Can you use a similar concept to loop thru xml files? I have an xml source, 2 sorts and a merge-join. Whenever I change the input file, I need to open my sorts and click ok. How will that work if I need to iterate thru a directory of files?

Changing DataTypes in an Excel Data Source

Hello.

I'm importing some data from an excel file to sql server 2005.

I created an Excel Data Source inside my Data Flow Task but it is assuming that the source columns DataType is double-precision float [DT_R8]. It isn't, even though some rows may containg numeric string in the column's cell.

If I go to the Data Sources advanded editor, and modify the data type property of the column, SSIS complains the the error output and the source output are not of the same DataType. If I try to change the error output's data type in the advanced editor I get this error: "Property Value". The deailed error states:

Error at MOVIM 04 [MOVIM 04 [1]]: The data type for "output "Excel Source Error Output" (10)" cannot be modified in the error "output column "Agente Protector" (7662)".

Error at MOVIM 04 [MOVIM 04 [1]]: Failed to set property "DataType" on "output column "Agente Protector" (7662)".

If i let SSIS correct the error by itself, it changed the dource column back to double-precision float [DT_R8].

Is there any way to get arround this?

Thanks in advance,

Hugo Oliveira

Hi,

By default SSIS will consider first 8 rows to determine the data type of each column. Refer http://support.microsoft.com/kb/189897/en-us regarding this. U can add "IMEX=1; MAXROWSTOSCAN=0" to your excel connection string to get around the problem. Hope it will work.

|||

Hello.

I've tried your tip and it's wotking well now.

Thanks,

Hugo Oliveira

|||

Adding IMEX=1 to the connection string made the following Error message to appear.

"Coulnd not find Installable ISAM"

I've been to http://support.microsoft.com/kb/209805 and the path in the registry key is correct has they say in the article.

Did anyone had the same problem ?

|||OK, IMEX=1 should be added to the extended properties of the Connection String.|||

Hi Thiru_ and Hugo

I added IMEX=1 in connection string!

When i have a column with simple data, for example:

A column with integer and string values it functioned correctly, but if will have columns with differents formatted cells the IMEX parameter doesn't function returning one data type default.

Some idea for this problem?

Thanks!

Andr Rentes
Brazil

PS. Hugo você brasileiro? Se for entre em contato para trocar idias sobre o SSIS, n?o achei nenhum fórum brasileiro sobre o mesmo. Meu email rentes @. gmail.com

|||

Hi,

I have a problem similar to this one. My excel contains data of the "general" type, mixing in the same column data that are by nature chars and ints: ex: 1, 2, ..., "5+". The automatic indentation of excel shows that implicitly the "5+" is treated as a char and the rest as numbers.

Depending on the first value and the IMEX setting, I can make SSIS consider the values of one of these 2 datatypes. When I use DT_NUMERIC I lose the "5+" which seems logical. But when I convert everything to char, even unicode DT_WSTR, I would have excpected that BOTH the numeric and string values are converted. But then I only read "5+" and not the rest.

I don't find a way either to read a column in twice, once with one data type and once with another.

Does anybody no a way around this?

Many thanks,

Jan

Changing DataTypes in an Excel Data Source

Hello.

I'm importing some data from an excel file to sql server 2005.

I created an Excel Data Source inside my Data Flow Task but it is assuming that the source columns DataType is double-precision float [DT_R8]. It isn't, even though some rows may containg numeric string in the column's cell.

If I go to the Data Sources advanded editor, and modify the data type property of the column, SSIS complains the the error output and the source output are not of the same DataType. If I try to change the error output's data type in the advanced editor I get this error: "Property Value". The deailed error states:

Error at MOVIM 04 [MOVIM 04 [1]]: The data type for "output "Excel Source Error Output" (10)" cannot be modified in the error "output column "Agente Protector" (7662)".

Error at MOVIM 04 [MOVIM 04 [1]]: Failed to set property "DataType" on "output column "Agente Protector" (7662)".

If i let SSIS correct the error by itself, it changed the dource column back to double-precision float [DT_R8].

Is there any way to get arround this?

Thanks in advance,

Hugo Oliveira

Hi,

By default SSIS will consider first 8 rows to determine the data type of each column. Refer http://support.microsoft.com/kb/189897/en-us regarding this. U can add "IMEX=1; MAXROWSTOSCAN=0" to your excel connection string to get around the problem. Hope it will work.

|||

Hello.

I've tried your tip and it's wotking well now.

Thanks,

Hugo Oliveira

|||

Adding IMEX=1 to the connection string made the following Error message to appear.

"Coulnd not find Installable ISAM"

I've been to http://support.microsoft.com/kb/209805 and the path in the registry key is correct has they say in the article.

Did anyone had the same problem ?

|||OK, IMEX=1 should be added to the extended properties of the Connection String.|||

Hi Thiru_ and Hugo

I added IMEX=1 in connection string!

When i have a column with simple data, for example:

A column with integer and string values it functioned correctly, but if will have columns with differents formatted cells the IMEX parameter doesn't function returning one data type default.

Some idea for this problem?

Thanks!

Andr Rentes
Brazil

PS. Hugo você brasileiro? Se for entre em contato para trocar idias sobre o SSIS, n?o achei nenhum fórum brasileiro sobre o mesmo. Meu email rentes @. gmail.com

|||

Hi,

I have a problem similar to this one. My excel contains data of the "general" type, mixing in the same column data that are by nature chars and ints: ex: 1, 2, ..., "5+". The automatic indentation of excel shows that implicitly the "5+" is treated as a char and the rest as numbers.

Depending on the first value and the IMEX setting, I can make SSIS consider the values of one of these 2 datatypes. When I use DT_NUMERIC I lose the "5+" which seems logical. But when I convert everything to char, even unicode DT_WSTR, I would have excpected that BOTH the numeric and string values are converted. But then I only read "5+" and not the rest.

I don't find a way either to read a column in twice, once with one data type and once with another.

Does anybody no a way around this?

Many thanks,

Jan

Changing DataTypes in an Excel Data Source

Hello.

I'm importing some data from an excel file to sql server 2005.

I created an Excel Data Source inside my Data Flow Task but it is assuming that the source columns DataType is double-precision float [DT_R8]. It isn't, even though some rows may containg numeric string in the column's cell.

If I go to the Data Sources advanded editor, and modify the data type property of the column, SSIS complains the the error output and the source output are not of the same DataType. If I try to change the error output's data type in the advanced editor I get this error: "Property Value". The deailed error states:

Error at MOVIM 04 [MOVIM 04 [1]]: The data type for "output "Excel Source Error Output" (10)" cannot be modified in the error "output column "Agente Protector" (7662)".

Error at MOVIM 04 [MOVIM 04 [1]]: Failed to set property "DataType" on "output column "Agente Protector" (7662)".

If i let SSIS correct the error by itself, it changed the dource column back to double-precision float [DT_R8].

Is there any way to get arround this?

Thanks in advance,

Hugo Oliveira

Hi,

By default SSIS will consider first 8 rows to determine the data type of each column. Refer http://support.microsoft.com/kb/189897/en-us regarding this. U can add "IMEX=1; MAXROWSTOSCAN=0" to your excel connection string to get around the problem. Hope it will work.

|||

Hello.

I've tried your tip and it's wotking well now.

Thanks,

Hugo Oliveira

|||

Adding IMEX=1 to the connection string made the following Error message to appear.

"Coulnd not find Installable ISAM"

I've been to http://support.microsoft.com/kb/209805 and the path in the registry key is correct has they say in the article.

Did anyone had the same problem ?

|||OK, IMEX=1 should be added to the extended properties of the Connection String.|||

Hi Thiru_ and Hugo

I added IMEX=1 in connection string!

When i have a column with simple data, for example:

A column with integer and string values it functioned correctly, but if will have columns with differents formatted cells the IMEX parameter doesn't function returning one data type default.

Some idea for this problem?

Thanks!

Andr Rentes
Brazil

PS. Hugo você brasileiro? Se for entre em contato para trocar idias sobre o SSIS, n?o achei nenhum fórum brasileiro sobre o mesmo. Meu email rentes @. gmail.com

|||

Hi,

I have a problem similar to this one. My excel contains data of the "general" type, mixing in the same column data that are by nature chars and ints: ex: 1, 2, ..., "5+". The automatic indentation of excel shows that implicitly the "5+" is treated as a char and the rest as numbers.

Depending on the first value and the IMEX setting, I can make SSIS consider the values of one of these 2 datatypes. When I use DT_NUMERIC I lose the "5+" which seems logical. But when I convert everything to char, even unicode DT_WSTR, I would have excpected that BOTH the numeric and string values are converted. But then I only read "5+" and not the rest.

I don't find a way either to read a column in twice, once with one data type and once with another.

Does anybody no a way around this?

Many thanks,

Jan

Changing DataTypes in an Excel Data Source

Hello.

I'm importing some data from an excel file to sql server 2005.

I created an Excel Data Source inside my Data Flow Task but it is assuming that the source columns DataType is double-precision float [DT_R8]. It isn't, even though some rows may containg numeric string in the column's cell.

If I go to the Data Sources advanded editor, and modify the data type property of the column, SSIS complains the the error output and the source output are not of the same DataType. If I try to change the error output's data type in the advanced editor I get this error: "Property Value". The deailed error states:

Error at MOVIM 04 [MOVIM 04 [1]]: The data type for "output "Excel Source Error Output" (10)" cannot be modified in the error "output column "Agente Protector" (7662)".

Error at MOVIM 04 [MOVIM 04 [1]]: Failed to set property "DataType" on "output column "Agente Protector" (7662)".

If i let SSIS correct the error by itself, it changed the dource column back to double-precision float [DT_R8].

Is there any way to get arround this?

Thanks in advance,

Hugo Oliveira

Hi,

By default SSIS will consider first 8 rows to determine the data type of each column. Refer http://support.microsoft.com/kb/189897/en-us regarding this. U can add "IMEX=1; MAXROWSTOSCAN=0" to your excel connection string to get around the problem. Hope it will work.

|||

Hello.

I've tried your tip and it's wotking well now.

Thanks,

Hugo Oliveira

|||

Adding IMEX=1 to the connection string made the following Error message to appear.

"Coulnd not find Installable ISAM"

I've been to http://support.microsoft.com/kb/209805 and the path in the registry key is correct has they say in the article.

Did anyone had the same problem ?

|||OK, IMEX=1 should be added to the extended properties of the Connection String.|||

Hi Thiru_ and Hugo

I added IMEX=1 in connection string!

When i have a column with simple data, for example:

A column with integer and string values it functioned correctly, but if will have columns with differents formatted cells the IMEX parameter doesn't function returning one data type default.

Some idea for this problem?

Thanks!

Andr Rentes
Brazil

PS. Hugo você brasileiro? Se for entre em contato para trocar idias sobre o SSIS, n?o achei nenhum fórum brasileiro sobre o mesmo. Meu email rentes @. gmail.com

|||

Hi,

I have a problem similar to this one. My excel contains data of the "general" type, mixing in the same column data that are by nature chars and ints: ex: 1, 2, ..., "5+". The automatic indentation of excel shows that implicitly the "5+" is treated as a char and the rest as numbers.

Depending on the first value and the IMEX setting, I can make SSIS consider the values of one of these 2 datatypes. When I use DT_NUMERIC I lose the "5+" which seems logical. But when I convert everything to char, even unicode DT_WSTR, I would have excpected that BOTH the numeric and string values are converted. But then I only read "5+" and not the rest.

I don't find a way either to read a column in twice, once with one data type and once with another.

Does anybody no a way around this?

Many thanks,

Jan