Hello all,
I currently started working on SSIS. Here is my first assignment; I am trying to get info from all servers. I was able to do it from one server, how can i extend this package so that my package touchs every server and get info from them.
Thanks
I have an SSIS job that dynamically loops through each server, grabbing data for typical DBA reporting, like diskspace, and errorlogs. If the server is down for whatever reason the SSIS package fails. Is there any way I can prevent the SSIS package from failing if one of the servers is down?
View 1 Replies View RelatedWe are trying to populate a table from tables on 2 different servers. We are using an SQL statement that runs fine on the reports server, i.e.
select a.field1, b.field2
from server1.db1.dbo.table1 a join server2.db1.dbo.table1 b on a.field1 = b.field2
Not sure if the syntax is right, but I have it right and working in a query. Anyway, when I try and run this via a SSIS package, is complains that it cannot make a connection to server2. Do I have to set up 2 data sources, or will SSIS use linked servers?
How do I create, say an "update statistic" maintenace package that works on multiple servers and databases?
For example, how do I get the package to operate on the following servers and tables:
ServerA, tableX, tableY, tableZ
ServerB, tableM, tableN
Also, I would like to save the package configuration and store it on SQL Server. So if I want to add more servers or tables to the list, the SSIS package will pick it up at run time.
Is this possible?
I'm trying to execute my package using schedule in SQL Server Agent, I've already tested my package by run it manually in Integration services and it works. The table created, the data from my flat file also inserted into the table correctly and the result return with success.
The question is why when I execute my package using SQL Server Agent, the SQL Server Agent keep executing my package like infinite loop until I stop the job. after I stop the job there is no error generated by sql server. Could you figured why this happen?? I've already tried to upgrade into SP2 and set the package protection level and still not get good result from it. thank you.
Can we drag and drop Existing SSIS package into for each loop container ?
View 3 Replies View Related
Hello
I am trying to use Foreach loop container - Foreach File Enumerator
Is there easy way to retrieve enumerator configuration folder from variable - so I can easily move package from server to server?
I would like to have name of the folder we getting files from retrieved from variable by using this control
Thank you in advance
Armine Bell
I have created a SSIS package with a Foreach Loop including a Data Flow Task, which in turn include a Row Count component which pass the row count value to variable with package scope. The variable is used in an Execute SQL Task following the Data Flow Task.
The package executes successfully when executed on its own, but when executed as a child from a parent package (which only include an Execute Package Task) the variable from the Foreach Loop becomes NULL.
There are a lot of other variables in the package receiving values dynamically without any problem, the row count variable however is the only variable in the package that receives a value as part of a Data Flow (and used in following tasks within the Foreach Loop).
Why does the variable become Null? For your information, I am using a variable with package scope and no variables from the parent package are used or passed from the child package to the parent package.
(For your information, we are running the 64 bit version)
Hey, I've a few jobs which call SSIS packages. If I run the SSIS package, it runs fine but if I try to run the job which calls this package, it fails. Can someone help me troubleshoot this issue? None of my jobs that call an SSIS package work. All of them fail.
Thank you
Tej
I have a MSSQL DTS Package where it needs to loop/execute 3 times because the main task is to import data from 3 excel files (different location) into 1 SQL table. I used a global variable vCounter and I use an ActiveX Script.
ActiveX Script 1
Option Explicit
Function Main()
Dim vDate, vCounter, vBranchCode, vPath
vDate="011207"
vCounter=DTSGlobalVariables("gVarCounter").Value
IF vCounter<=3 THEN
IF vCounter=1 THEN
vBranchCode="ALB"
vPath="D:PROJECTSHRISALB"
ELSEIF vCounter=2 THEN
vBranchCode="MOA"
vPath="D:PROJECTSHRISMOA"
ELSEIF vCounter=3 THEN
vBranchCode="PSQ"
vPath="D:PROJECTSHRISPSQ"
END IF
DTSGlobalVariables("gVarPath").Value=vPath & vDate & "_" & vBranchCode & ".xls"
Main = DTSTaskExecResult_Success
ELSE
<This is where i will initialize the global variable gVarCounter, so in the next execution..the value should be back to 1>
DTSGlobalVariables("gVarCounter").Value=1
<DTS Process should stop execution...how is this?>
END IF
End Function
After excel to sql dts
ActiveX Script2
Function Main()
IF gVarCounter<=3 then
DTSGlobalVariables("gVarCounter").Value=DTSGlobalVariables("gVarCounter").Value+1
DTSGlobalVariables.Parent.Steps("DTSStep_DTSActiveScriptTask_1").ExecutionStatus=DTSStepExecStat_Waiting
Main = DTSTaskExecResult_Success
END IF
End Function
Thanks a lot.
Hi. I am running a package using 'execute package' inside a for each loop file iteration. I have to iterate through some 10,000s of files to ETL source data, a resonably complex package. I want to set going and get some sleep but every 200 files or so I get a memory exhausted error and the task fails. Is there a short easy fix to refresh memory each iteration. If so please let me know. Just ignoring the failure at task level won't refresh the memory, will it?
Thanks for the help.
In Dts you can loop a package by creating an Active-X script with something like this ...
"oPackage.Steps("DTSStep_1").ExecutionStatus = DTSStepExecStat_Waiting"
"oPackage.Steps("DTSStep_2").ExecutionStatus = DTSStepScriptResult_DontExecuteTask"
What is the way to do in SSIS? Depending on a condition i want to rerun my package but have not found a smooth way to do it. I guess you can do it in a better way than using the ActiveX Script Task
have a nice day
/Erik
Okay, can someone explain why when I execute children packages within a loop that only a couple of them work?
When I first started this I had Child Package 1 and Child Packge 2 working from the Parent Package 1. It would loop through twice (I had 2 organizations in my outer loop) and pass in the Parent Package variables correctly.
I am using a simple "Foreach Item Enumerator" with a collection of string enumerators that are the Names of the children packages - these are assigned to a variable that is scoped to the outer loop.
Now, when I add a new Child Package 3 and set it up the same as the other 2, when it goes to execute Child Package 3 it fails with the error:
"Error: The connection manager "[My Package Name].dtsx" is not found. A component failed to find the connection manager in the Connections collection. "
What in the world have I done? I was under the assumption that since the first 2 packages worked, that any other packages I added would work the same.
I have an SSIS package which is suppose to read this db file and insert it into the SQL 2005 table. But when I execute this package it goes into a loop, reading the file over and over again. The package fails after inserting some 10 million rows ( actual rows are not more then 270,000) giving the error msg 'Not enough space on temporary disk'.
On examing the data transfered into SQL there are duplicate rows.
I also used the import export wizard (thinking there might be some error in Package code) provided by SQL to transfer the data from .db to SQL but it has the same result(goes in a loop).
I would appreciate any help in this problem. Let me know if you have any other questions.
Thanks
Hi,
In our project we have two SSIS package.
And there is a task (Execute SSIS package) in First package that calls the execution of second package.
I m continuously receiving an error "Failed to decrypt protected XML node "PackagePassword" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that the correct key is available."
As we are running first package by job, job runs successfully logging above error
The protection level of second package is set to "EncryptSensitiveWithUserKey"
Can anybody please suggest how to handle it?
How can I use a foreach loop container with an Excel connection manager. It is virtually impossible to configure the ConectionString property through expressions builder. Everything works fine with a Flat File connection manager but when it comes to Excel, I get the following error message: Error 1 Validation error. Extract Four: Extract Four [1]: The AcquireConnection method call to the connection manager "Excel Connection Manager" failed with error code 0xC0202009. Tarification.dtsx 0 0
Please ! Heeeelp ! tel me how I can loop through Excel files !
Hello,
I'd need an help because I'm stucked!!
I have to import an Excel file into my DB.
The Excel file is made by 2 worksheets but I need only one and inside this worksheet I have to loop through the columns and for each column I define a Data Flow that trasform the data as necessary and then insert into the table.
I started with a "Foreach ADO.NET Schema Rowset Enumerator" with connection=excel file and the schema was set to "Columns" but the loop go also through the worksheet that I don't need..
after 4 hours of tries I'm lost...
Someone could give me an advice?
ThankX
Marina B.
I have just set up an SSIS package similar to this URL....I have 5 connections in a SQL table which i want to loop through, set each string to be a connection string in the package (variable ConnString) and run a select to pull back information and store in a central table.
The package runs successfully and connects to 5 instances, but it connects to the same instance each time, my local one which is set as the value for variable used for the connection string. How is the Connection string supposed to get passed from the @SourceList variable to the @Connstring variable? Its clear what the problem is, as the results of my query as step one which pulls back all 5 strings just isnt getting populated from @SourceList into @Connstring one by one.
SSIS package =
1) SQL Task - Select connection strings from SQLTable and store results in @sourcelist
2) for each loop - ADO Enumerator, rows in first table @Sourcelist
3) data flow task within the loop - OLE DB Source, dynamic connection as connection manager, running the query i want from a variable, with the destination set to my central database.
Hi,
I am using For each loop container in my SSIS package which will loop through more than one input file in the input folder.
All the input files in the input folder are picked by one by one. All the input files in the input folder are executed with the same ExecutionInstanceGUID.
Now, my requirement is to get a new ExecutionInstanceGUID during the execution of each input file in the For each loop container. My package only contains the For each loop container with some logic inside that.
Please let me know if any one can help me for the same.
Regards,
Sriram.
Hi All,
I have a SSIS package which contains For Loop Container. when a task in the for loop container fails iam setting loop value to 0, even though iam not able to exit the loop. can some help me
Note: when ever the a task in the Loop fails the loop should stop and package should stop.
WHAT IS THE USE OF THE FOR LOOP CONTINER
PLEASE GIVE ME THE EXAMPLE
REGARDS
KOTI
Could someone send me any links or information on how to loop through an ADO.NET dataset in SSIS? I need step by step information please.
Thank you,
Shiva
I would like to loop through a SQL Server table that contains the paths to all the reports we need to run and then execute the reports via SSIS. What task should I be doing to do this? Will the For Loop work for something like this?
View 9 Replies View RelatedHello,
I have a resultset, which I pass to a loop container, to use as parameters in an ExecuteSQL task for each row.
However, I would like to do some parallell processing, rather then iterate through them sequentially. How can I achieve this? Is there a way I can make the loop iterate before sql task is finished, or should I be using soemthing else entirely?
I hope this makes sense.
Can anyone point me in the right direction please?
Hey Guys.
I am new to using SSIS and need some assistance. I am trying to create a package that will loop through a folder of DBF files and import them into SQL server. Here is what I did so far:
- Created a Foreach Loop Container with a Data Flow Task within
- Created a string variable to store the file names
- Created the OLE DB Source and Destinations
- Created the connection manager with dBASE III Extended Property pointed to the folder where the files are.
When I try to set the OLE Source to use the file name variable, I get an error stating that there is no Destination table defined. I get the same error when I try to setup the OLE Destination. How do I resolve this issue? I want it to import each file into its own table. Is there any way to reuse this file name variable as the destination table name?
Any help would be greatly appreciated.
Thanks!
-Michael
I have a SSIS job, one of the last steps it performs is to execute a SQL 2000 DTS package. This has to be done as a SQL 2000 DTS package as it is performing rebuilds of SQL 2000 Analysis Services dimensions and cubes. We've found that when the DTS fails the SSIS job is happily completing showing as a success, we would prefer to know it went wrong.
As far as I'm aware SSIS merely starts the DTS off and doesn't care about it's result. I've taken a look in to turning on the logging for the execute DTS package and thought that the ExecuteDTS80PackageTaskTaskResult would give me the answer I need...but is merely written to the log not available as an event-handler. It also looks like it is not safe to put a SQL task in as the next item to go look at the SQL 2000 system tables to look at the log for the DTS package as the SSIS documentation warns that the DTS package can continue to run after the execute DTS package task has ended.
Ideally I want any error raised within the DTS package to cascade up to be an error in the SSIS job, I can then handle it appropriately. I cannot find a way to do this. Is there a way?
If not, can anyone suggest how in the remainder of the SSIS tasks I can be sure that the DTS has completed before I start any other tasks that will check for the SQL 2000 log of its execution?
Hi,
I have developed an SSIS package for ETL purpose. I am invoking the SSIS package through .Net console application by referencing the ManagedDTS Assembly. I am able to execute the package in Sql Server 2005 Developer Edition and it runs fine till completion.
But when i try to execute the packahe in Sql Server 2005 Standard edition, by invoking the package through .Net console application the status of the package is failure.
Can any one help me how to over come this problem.
Hi All,
I am in the process of moving from a 32-bit SQL Server 2005 Enterprise (9.0.3054) to a 64-bit SQL Server 2005 Enterprise (9.0.3054 with 4 CPUs and 8GB of memory on Win 2003 SP2) and the process has been very frustrating to say the least. I am having a problem with packages that I created on my 64-bit SQL Server. I am importing a few tables from the 32-SQL Server into the 64-bit SQL Server using the Task --> Import to create the package.
Sometimes when I am creating a package I get the following error in a message box:
SQL Server Import and Export Wizard
The SSIS Runtime object could not be created. Verify that DTS.dll is available and registered. The wizard cannot continue and it will terminate.
Additional information: Attempted to read or write protected memory. This is often an indication that other memory is corrupt. (System.Windows.Forms)
Other times when I run a package that has run successfully before I get the following error:
Faulting application dtexecui.exe, version 9.0.3042.0, stamp 45cd726d, faulting module unknown, version 0.0.0.0, stamp 00000000, debug? 0, fault address 0x025d23f0.
Other times I get this error message:
.NET Runtime version 2.0.50727.1433 - Fatal Execution Engine Error (79FFEE24) (80131506)
And still other times
The package appears to hang when running. By this I mean that the Package Execution Progress shows progress up to a point then it just stops. (The package takes about 17 seconds to run normally) CPU usage is at 1% and the package cannot be stopped.
I have deleted and re-created the package several times and I have also re-installed the service pack on the SQL Server (9.0.3054) but that did not help.
Does anyone have any other suggestions to try?
Thanks.
I would like to standardize SSIS development so that developers all start with the same basic template. I have set it up so it is an available template ( http://support.microsoft.com/kb/908018 ) but I would like it to be the default when a new project or package is created. Is this an option?
View 4 Replies View RelatedI would like to fetch the data flow component name while package is executing. Since system variable named [System::SourceName] only fetches name of the control flow tasks? Is there a way to capture them?
View 5 Replies View RelatedHi there
We have a SSIS run which runs as follows
The master package has a configuration file, specifying the connect strings
The master package passes these connect-strings to the child packages in a variable
Both master package and child packages have connection managers, setup to use localhost. This is done deliberately to be able to test the packages on individual development pc€™s.
We do not want to change anything inside the packages when deploying to test, and from test to production. All differences will be in the config files (which are pretty fixed, they very seldom change). That way we can be sure that we can deploy to production without any changes at all.
The package is run from the file system, through a job-schedule.
We experience the following when running on a not default sql-server instance (called dkms5253uedw)
Case 1:
The master package starts by executing three sql-scripts (drop foreign key€™s, truncate tables, create foreign key€™s). This works fine.
The master package then executes the first child package. We then in the sysdtslog get:
Error - €œcannot connect to database xxx€?
Info - €œpackage is preparing to get connection string from parent €¦€?
The child package then executes OK, does all it€™s work, and finish. Because there has been an error, the master package then stops with an error.
Case 2:
When we run exactly the same, but with the connection strings in the config file pointing to the default instance (dkms5253), the everything works fine.
Case 3:
When we run exactly the same, again against the dkms5253uedw instance, but now with the exact same databases defined in the default instance, it also works perfect.
Case 4:
When we then stop the sql-server on the default instance, the package faults again, this time with
Error - €œtimeout when connect to database xxx€?
Info - €œpackage is preparing to get connection string from parent €¦€?
And the continues as in the first case
From all this we conclude, that the child package tries to connect to the database before it knows the connection string it gets passed in the variable from the master package. It therefore tries to connect to the default instance, and this only works if the default instance is running and has the same databases defined. As far as we can see, the child package does no work against the default instance (no logging etc.).
We have tried delayed validation in the packages and in the connection managers, but with the same results (error).
So we are desperately hoping that someone can help us solve this problem.
Thanx,
/Nils M - Copenhagen
hi,
I am interested in Passing value from a child Package variable to the Parent package that calls it in ssis.
I am able to call the Child package using the execute package task and use Configurations to pass values from the parent variable to the child, but I am not able to pass the value from the child to the parent.
I have a variable called datasetId in both the parent and child. it gets computed in the child and needs to be passed to the parent...
Any suggestions?
Thanks for any help in advance..
smathew
Deployed Report having SSIS package as source do not work when Indirect Package configuration is used in ETL package. It seems ETL package when called/executed from Report manager does not recognize environment variable to pick up the dtsconfig file.
The Report works when Direct package configuration is used to same dtsconfig file.
What could be the reason? Any solution for this? This will cause our build/deployment to QA and Prod very difficult.