Hi All,
I transfed one dts pckg to ssis, in this pckg i have one script that script variable i need to set into ssis. here is following script code
Add your code here
' Source Type = 0
' INI file name = E:mssqldatamember_updatemember_load.ini INI file key = source_db INI file section = db_config
' Destination = 'Global Variables';'abc_db';'Properties';'Value'
' ***************************************************
' Source Type = 0
' INI file name = E:mssqldatamember_updatemember_load.ini INI file key = source_dwdb INI file section = db_config
' Destination = 'Global Variables';'xyz_dwdb';'Properties';'Value'
' ***************************************************
' Source Type = 0
' INI file name = E:mssqldatamember_updatemember_load.ini INI file key = target_db INI file section = db_config
' Destination = 'Global Variables';'bed_db';'Properties';'Value'
' ***************************************************
' Source Type = 0
' INI file name = E:mssqldatamember_updatemember_load.ini INI file key = crdir INI file section = file_backup
' Destination = 'Tasks';'DTSTask_DTSCreateProcessTask_1';'Properties';'ProcessCommandLine'
' ***************************************************
' Source Type = 0
' INI file name = E:mssqldatamember_updatemember_load.ini INI file key = cpfile INI file section = file_backup
' Destination = 'Tasks';'DTSTask_DTSCreateProcessTask_2';'Properties';'ProcessCommandLine'
can you tell me how to set this global variables into ssis pckg and also can you expain me what is source type=0 or 1,2,3,4 up to what number.
I am trying to create an ssis package with dynamic csv file as output. and out format contains query output.
sample file name:
Unique identifier + query output + systemdate();
The expression is looking like this.
@[User::FilePath] + @[User::FileName] + ".CSV"
-- user filepath is a variable from ssis package. File name is the output from SQL query. using script task i have assigned the values to @[User::FileName] .
When I debugged the script task the value getting properly but same variable am using for Flafile destination. but its not working.
I want to see if anyone in this community has come across this and has found a solution.
we have 3 different SQL Servers because we are still porting over SQL 2k databases 2k5. We have multiple databases of course. We would like to create some kind of template that would create the connection manager based off of the information in our XML configuration file. We are thinking we would have 2 files for this. One would house the actual server information but omit the database name and username/password. The second will be a list of all the databases with their usernames/passwords for each one.
If we put all of these in the configuration files and do not have a connection for each one, SSIS will throw the error stating it is missing connection managers for those items. We would like to see about maybe specifying the database name and have the connection manager dynamically pull the database specific information into the package. This would also need to be able to handle more then 1 database should there be the need.
I am new to SSIS. It seems pretty nice and extensible but my learning curve on it is horrible as I never programmed DTS packages before.
I hope I am making sense here. Thanks for any help offered!
I am trying to to make a dynamic column mapping using the SSIS, the mapping will be stored in a seperate table, and based on the file name, the necessary mapping will be applied.
Hey there, I know that many articles have been written describing configurations for packages but I have yet to have found one that describes if it is possible to use SQL Server configuration type for a package that is to be tested on DEV, then UAT, then PROD boxes.
I would like to know if there's a way to store values in a config table in a database on DEV, UAT and PROD but never have to change anything in the package.
I mean, I wonder if I can pass a parameter that defines the server to go get the configurations from. The 3 servers will contain a database with a config table named the same on each server but have different configuration values to point them to the proper sources and destinations depending on which server the configuration database resides.
My source table is Oracle .we want to have dynamic query the following steps we have done.
a.Created a new variable called StrSQL & ProductID variable contains values for where clause. b.Set EvaluateAsExpression=TRUE c.Set Expression=""select * from prod where product_id = " + @[ProductID] d.OLE DB Source component, opening up the editor e.Set Data Access Mode="SQL Command from variable" f.Set VariableName = "StrSQL"
But i am getting the following error
Error at DataFlow Task[OLEDB Source[1]]:An OLEDB error has occured, Error code: 0x80040e14. AN OLE DB records is available . Source "Microsoft OLEDB Provider for Oracle" Hresult: 0x80040e14 Description : "ORA-00936:missing expression
What could be the problme.how about the support of dynamic query (Oracle) in ssis?
2.Clarification in Lookup.
--------------------------------
How to Pass Parameters to Lookup. (Dynamic sql in Lookup)
I have 5 SSIS Packages out of which 1 is the parent package which calls the other 4 packages.
There are some database connections in my child packages.
Now, I want to make the packages dynamic.
I have two options.
1) Have all the connection information in the parent package and pass those values to the child packages. This way I'll be having only 1 configuration, the one for the Parent.
2) Have a separate configuration file for each package that will have all the connection related informations.
I have a SSIS Package that exports data from Sql Server to an Excel file. I need help figuring out how to have the file name be "Report_02132007.xls". Basically I want to append the date to the file name. Any ideas?
I have an SSIS package that has a variable called @RUN_DATE.I would like to create a sql agent job and uses the set values tab to pass a dynamic parameter to the ssis package. for example, i would like to always set the variable to the last day of the previous month. I know that If i hardcode the date as 5-31-2013 it works fine, but i have not been able to figure out how to make the value dynamic by using an expression or if this is even possible.
Hi All, i've been reading this article http://rafael-salas.blogspot.com/2006/12/import-header-line-tables-_116683388696570741.html
in regards to creating an excel spreadsheet dynamically in SQL Server 2005 SSIS. However, i'm constantly getting an where the tab is created but not being populated. Can somebody post up a clearer example?
The problem I'm trying to solve is to automate the export of a query onto a new dynamic spreadsheet each time I run this SSIS package.
1) We are doing data migration from SQL Server 2000 OLTP design to SQL Server 2005 OLAP design.
2) We have used SSIS packages and data flow tasks in which we mentioned connection strings for source and target containers.
3) We have a master execute package which contains series of execute packages in relational order.
4) When we execute this master package, we should be able to dynamically specify different source and connection strings for all packages.
5) Can we do it with dtexec option /Conn[ection] IDOrName;ConnectionString We know what to give for ID or Name but what do we give for ConnectionString. Will it be the source/target one? Can we specify both source and target ones with which the data flow packages get executed.
Dear Friends, I currently have my excel source dynamic for the connectionstring. Using a global variable with the filename and using the expressions of this control to dynamically have the connecttionstring. My problem is that i need tto read always the first workseet and the name changes frequently and generates an erro in SSIS. how can I set the excel source to goes only for the first sheet independently of the worksheet name?! Regards!!
I have a database with serveral tables, for example 'customer', I want to update this table with a SSIS package. However, to ensure we don't have issues if the update fails then I've put in an intermediate stage
Using an Execute SQL Task I create temporary tables, for example 'customer_tmp'. Data is then imported into these tables. When all the data is imported successfully the original tables are dropped and the temporary tables are renamed, removing the '_tmp'
This works fine and I'm happy with it. However, if someone adds a column to one of the tables in SQL server it is lost on the next upload.
Similarly I have to hard code creating the indexes into the package as well.
Does anyone know how I could copy the original table definitions and create the temporary tables dynamically. So that any new columns would be picked up?
And indeed is it possible to copy the indexes from one table to another before the drop and rename trick?
Possible or not? --> I maybe lazy - but I want to achieve just specifiying 1 variable in SSIS package ("environment") - and all the connectionStrings should "poof" magically be adjusted to correct locations
In DTS I created a SetDTSenvironmentVariables function for all my packages - so how wouldIi achieve this in SSIS?
Function SetDTSenvironmentVariables( environment ) Folder = "MyDtsPackageFolder" Select Case environment case "DEV" DTSGlobalVariables("WorkingDirectory").value = "C:Packages" & Folder case "STAGING" DTSGlobalVariables("WorkingDirectory").value = "D:Sql_working_directoryMy_productionSTAGING" & Folder case "LIVE" DTSGlobalVariables("WorkingDirectory").value = "D:Sql_working_directoryMy_production" & Folder End Select
' ' Set Connection Properties ' dim oPackage, oConn set oPackage = DTSGlobalVariables.parent oPackage.LogFileName = DTSGlobalVariables("WorkingDirectory").value & "LogsErrors.txt" For Each oConn In oPackage.connections Select Case oConn.Name case "My_DB" Select Case environment case "DEV" oConn.datasource = "SERVER01" oConn.Catalog = "My_Production" case "STAGING" oConn.datasource = "SERVER06" oConn.Catalog = "My_Staging" case "LIVE" oConn.datasource = "SERVER06" oConn.Catalog = "My_Production" End Select case "Schools.xls" oConn.datasource = DTSGlobalVariables("WorkingDirectory").value & "" & "School_Codes.xls" case else oConn.datasource = DTSGlobalVariables("WorkingDirectory").value & "" & oConn.Name End Select Next
set oPackage = nothing set oConn = nothing End Function
In my destination table i am having some 30 columns and the CSV files what i get may have 10 columns or 20. How do I map columns between source and destination dynamically?
I have CSV file as source for SSIS package every time the filename will be changing like trd_1990M1_1990M12.csv,trd_1991M1_1991M12.csv , trd_1992M1_1992M12.csv etc.,
so it will vary as per user selection . i need to run the same SSIS package to execute the different file name with the same structure.
Please let me know the solution for that how to pass the file name dynamically to SSIS package.
I have a parent package which accepts date as input. I can configure Set Values of Execute Package Utility with hard coded date value and it works fine. My question is how do I configure Set Values to accept dynamic date value or current date value (may be using GetDate()) ?
I did a few searches but did not find this specific scenario. Can anyone state with confidence whether this is possible (and if so, how)? Scenario:
-One table with a couple million rows (one column indicates which Country the record belongs to)
-Need to create an Excel 2007 file dynamically (for each Country) using SSIS 2005. Filename should include the Country Name (Sweden_Affliliated.xlsx). I have a table that contains a distinct list of all the countries. Each worksheet will have the same structure / schema across all files.
What seems to be working:
**I understand how to use an Execute SQL task to get the list of Country Names and bind to an object variable. **I understand how to set variable mappings for a String variable to contain the "current Country" in a ForEach Loop. **I understand how to set the OLE DB Data Flow Source to use a SQL command from a String variable that has the CountryName dynamically embedded within it. **I understand I need to convert my varchar to Unicode using a Data Conversion task in my scenario. **I understand that in order to write to an Excel 2007 file I need to use an OLE DB Destination with an Extended Property value of "Excel 12.0" and the ServerName property should contain a path to the file with no quotation marks.
Problems I have:
**OLE DB Destination: How do I set up the mappings when the file does not exist yet?
What I want to avoid, is having to create a template source XLSX file and using a File Copy task (I have gone this route before, but it would be best if I did not require a template). Is there a way to configure the SSIS package without using a File Copy Task? Creating the Excel file on the fly?
I'm trying to make my SEND MAIL TASK in SSIS dynamic, by retrieving SMTP Server name (amongst other email message information) from a table.
I've got an SP that is getting the information into package variable's, and when I don't try and have a dynamic server name (i.e. I use a specified SMTP server in my SMPT Server connection), then the email get sent off.
I'm trying to use an expression to build alter the send mail to use the server name I have in a user variable.
I've tried using the expression for the property: SmtpConnection set to: RTRIM( @[User::MailServer])
I also tried without the RTRIM - I only added it because the error messages seem to include a full 'string' length, and there doesn't seem to be a 'varchar' data type for SSIS variables.
so: a> Is SMTPConnection the property I need to be setting? None of the others seem correct b> If SMTPConnection IS the property I need, does the value give the SMTP Server Name, or just the Name of the connection from the Connection Manager's?
Thanks
Part-Answer to: b> The SMPTConnection property sets the name of the CONNECTION to be used. That Connection needs to set the name of the server. So n ow, the question becomes, how do I configure that from SQL (I know how to use package configurations from XML files).
I have created an SSIS package that is designed to move data from SQL Server 2000 to an Access db. I have set the package up to accept four parameters. They are:
the name of the SQL Server Database, which is used in an expression to provide the source connection manager connection string;
the full path to the Access db destination database which is used like #1 above.
the SELECT statement used in an OLE DB Source object to get the data from the source table
the table name which is used by an OLE DB Destination object. I know that the source and destination tables have exactly the same structure and do not require a transformation.
In order to change variables 3 and 4 from above and have it work, I go through the following steps:
I change the variables to appropriate values.
Go to the Advanced Editor for the OLE DB Source object and click on "Refresh". This produces an error in the OLE DB Destination object that is something like "Validation error. Data Flow Task: DTS.Pipeline: input column "strRECTYPESUFFIX" (301) has lineage ID 17 that was not previously used in the Data Flow task.
Go to the Advanced Editor for the OLE DB Destination which brings up the "Restore Invalid Column Reference Editor". I mark all the columns that show up with the option to <Delete invalid column reference> and click OK
I then reopen the Advanced Editor for the OLE DB Destination, go to the 'Column Mappings' tab click 'Refresh', then in the upper pane where the input and output columns list appear I right click and choose "Map Items by Matching Names" At this point I no longer have the error and the package will execute without any problems.
I am doing this so that I can load the SSIS package in VB.NET (2.0) so that I can then set these variables programmatically and then execute the package. The problem is that this actually performs steps 1 and 4 above. 2 and 3 are left out and the package fails miserably.
I have found some information that would be helpful if I could get my hands on the appropriate object. I realize that the data flow component would return VS_NEEDSNEWMETADATA from the Validate method, and that could be repaired with the ReinitializeMetaData method of the data flow component ( I assume that is the object to be using in this case). But I do not know how to grab the "Data Flow Component" as an object based upon the "Package" object I have loaded so that I can check if it is valid and manipulate it if necessary.
I am working on FTP TASK in SSIS Package. i have to get files from FTP that file names are like 20141110.txt. i want to download any particular date file from ftp. How to i set expression in Remote path?
updating a recordset contained in an System.Object variable during runtime.
I am trying to execute multiple file actions (plus parsing those files into a set of staging tables) at separate locations in parallel. I know I can do this in C# but I have a business requirement to use SSIS for all ETL operations.
Any one site can have 0 to many of 1 to 3 files. I would like to run multiple sites at the same time, so when all files of all types are completed at that site then go on to the next site in the list. I know I can do a single site at a time in a foreach loop but if I can run lets say 3-5 sites concurrently then I should be able to save execution time.
My thought is to have a recordset of the sites, when any 1 of the 3 (or more) "control flows" is open, update the recordset to let it know that site being actioned, when that site is complete, update the recordset that the site is completed, and so on.Or am I running in the wrong direction?
I have a dts package that currently uses a dynamic properties task to set the values of global variables. Each variable is based on the value of a query to the database.
I am in the process of migrating this dts package to SSIS but cannot find an equivalent function. I have looked at property expressions but cannot get this working the same way.
I'm currently trying to convert over packages from SQL 2000 to SQL 2005. The biggest obstacle at the start has to do with converting my "Dynamic Properties" control. I use it to read an .ini file and load the user name and password to my connections. With 2000 it's nice and easy 1 file 3 lines. While trying to convert it I€™ve had nothing but problems. I've tried the Registry Entry but it forces everything into the Current User and I can't use that I need local machine. There is no documentation and I can't have a xml file with 1000 different username and password settings because 1 person has to update that file with the new passwords. Any help would be GREATLY appreciated.
I'm a newbie to SSIS so this question may cause people to point and laugh. I apologise if that's the case.
I have a configuration database that holds connection details to a remote database. I want to use these connection details to dynamically create a new connection to some remote database.
I usually work in with C# where this would be simple but I can't find how to do this using SSIS.
I have found people using configuration files but as I already have my connection data in a table I don't really want to strip it out. (Also, is it a security risk to have database connection strings stored in a config file?)
I have been able to implement a custom script object to build the connection string and assign it to a user variable within the package but can't find any way to then use that variable to connect to the remote database.
I have a problem with SSIS reading an environment variable after deploying the packages to a server. I explain.
I have an Parent Packages ETL_MAIN_PACKAGE.dtsx that reads the child packages from a record set and loops on it to execute them with the Execute Package Task task. The first child package to be executed is called DIM_PERIODIC.dtsx.
On my local machine, the Parent Package is configured to read its database connections from an XML file SSIS_configfile.config located on my C: drive. The path (C:SSIS_configfile.config) to this file is stored in the environment variable BI_ETL.
When I run the Parent Package inside SSIS only machine, the connections are read and the package executes perfectly. Now, I want to deploy the packages on our server.
I copied the XML configuration file to the server C drive, I created the same environment variable BI_ETL and set its value to C:SSIS_configfile.config and I rebooted the machine (in case).
The execution of the Parent package is managed by a stored procedure. I use xp_cmdshell command. The command line generated is :
This command generates an error telling that the Environment variable is not found and it throws this error:
Error : 2007-08-23 18:59:10.25 Code : 0x80019003 Sourse : The configuration environment variable was not found. The environment variable was: BI_ETL. This occurs when a package specifies an environment variable for a configuration setting but it cannot be found. Check the configurations collection in the package and verify that the specified environment variable is available and valid. End Error
Description: The file name "C:ETL_Deployment" /SET Package.Variables[P_LOOKUP_PATH].Value;C:ETL_DeploymentETL_LOGSDIM_PERIODIC.dtsx" specified in the connection was not valid.
End Error
I run the package on the same server with a command line directly in a DOS window:
Description: The file name "C:ETL_Deployment" /SET Package.Variables[P_LOOKUP_PATH].Value;C:ETL_DeploymentETL_LOGSDIM_PERIODIC.dtsx" specified in the connection was not valid.
End Error
I conclude that the environment variable is not read at all.
Does anybody have an idea on how to solve this problem ?
I can set almost all properties of FTP task and FTP connection manager using expressions option. In that option, I don't see that I can set FTP PASSWORD using a variable. How do I set the password dynamically?
We are writing SSIS packages to pull data from MSO€™s through a stored procedure and drop the output into the pipeline to store it on our local database.
Here is an example of a very simple data flow task that executes the following query in step 1
This command is stored in a variable called SQLOrgCommand. When I open the task and click on Columns I do not see any columns returned This allows me to conclude that the metadata needed from the GetCustomerData is not understood by SSIS because the SP uses dynamic sql.
While profiling we realized that it executed the following SQL:
SET FMTONLY ON
to get the metadata. However when we ran the sp with FMTONLY ON we recieved the following errors
Msg 536, Level 16, State 5, Procedure sp_GetData, Line 100 Invalid length parameter passed to the SUBSTRING function. Msg 536, Level 16, State 2, Procedure sp_GetData, Line 101 Invalid length parameter passed to the RIGHT function. Msg 536, Level 16, State 5, Procedure sp_GetData, Line 116 Invalid length parameter passed to the SUBSTRING function. Msg 536, Level 16, State 2, Procedure sp_GetData, Line 117 Invalid length parameter passed to the RIGHT function. Msg 536, Level 16, State 5, Procedure sp_GetData, Line 187 Invalid length parameter passed to the SUBSTRING function.
The same stored procedure executed without FMTONLY OFF generates the proper output.
Is this a known bug in SQL Server? If so is there a patch that we can install.
