pad the generated flat file with 0

Hi Guys,

Related to my last questions on SSIS work i'm doing, Is there a way to pad 0 on my generated flat file dynamically.

I'm getting the data from 1 table and then generating the file. The file i need to generate would have data at the desired location as the file is being used by another system.

Depedning on the data I want to put the padding of "0" and "3" inc certain fields. How am i suppose to do it.

Apart from this I would need to megre 2 or more column and before the merge do an airthmatic operation.

What would be the best component to use script component or derived column?

Ta

Gemma

Gemma,

Not sure if this will help or not, but it is how i've padded zero's before...

Use a derived column:

@.LENGTH is the length you would like to pad to)

@.MyStr is the character expression or string you would like to pad

SUBSTRING(REPLICATE("0", @.LENGTH), 1, (@.LENGTH + 1) - LEN(TRIM(@.MyStr))) + TRIM(@.MyStr)

|||

Here's another - using the same variables as above:

RIGHT(REPLICATE("0",@.Length) + @.MyStr, @.Length)

|||

Hi Guys,

I don't know whether this is completely what I asked.

I would need to pad the values dynamically. By this I mean the padding would be done depending on the actual length of data in the columns. So when the file is generated I would have to check each column and if certain column's data length is less then fixed length then pad it with zero.

Other then that how would I suppose to use these variables?

That is why I asked Script or derived column which one.

Just a quick note, the file I'm generating is going to be fixed width as i guess it and i'm generating a dummy file but with fixed width it's not putting the records in each line. Instead file is having all the records at the same line. Why? I've already checked. There are 2 columns which i'm writing. The first i'm putting in as 20 and the 2nd one as 35 width still i'm getting this problem.

Ta

Gamma

|||

The above expressions do exactly what you asked. The variables are simply placeholders in the code. If you know that your column is 20 wide and you want it to pad out the non filled data to 20 places (to the right) you would simply put your column name in the @.MyStr and 20 in the @.Length.

for example we have a membership number that needs to be padded out to 9 digits. The column's name is Member.

SUBSTRING(REPLICATE("0", 9), 1, (9+ 1) - LEN(TRIM([Member]))) + TRIM([Member])

So, what that does is takes a string of 9 zero's (Replicate("0", 9) and substrings out the 1, to 9 - trimmed column length and then adds the trimmed column information.

i.e. member number of "12345 " would yeild substring(replicate("0", 9), 1, 10 - 5) + "12345" > "0000" + "12345" > "000012345"

You would need to set this up for each column seperately.

hope this helps.

sql

Pad Spaces in FFConnection properties?

This is a considerably LOW priority, but is there a way to padd spaces in ssis flat file connection properties? We have cases where a CSV file will read in and throw a truncation error because the field length is greater then defined. However, the CSV file might look like ,"ABC ", where a number of trailing spaces are found. Is there some way to handle this without having to adjust teh field length, maybe ignore whitespace at the source?There is no way at the Flat File Source to ignore trailing whitespace, however there is a work around. Say in this case your field length is 3, and if there are trailing spaces after that, you want to ignore them. You can then use "Ignore Failure" for Truncation Error Disposition. That is, in the Error Output tab for the Flat File Source, for Truncation of this column, choose "Ignore Failure", instead of "Fail Component". By doing this, it will only get the specified characters, and ignore the rest, whether they are trailing white spaces, or some other data there. You have to make sure that this is not causing you to accept some data which you otherwise wanted to fail on really.|||

That is partially the problem, we only want to ignore whitespaces, but if there is actual chars then we would want to throw truncation error.

Thanks for your thoughts...

Packages, Jobs and Scheduling issues

I'm starting to have second thoughts about switching to SQL 2005.

After hours of trying, I finally managed to get SSIS packages to run as intended. Now, I need to create a schedule for the package. I create a job, owned by the Local System account, which SQL Server Agent also uses. I can run the Package myself, but when I schedule it, it always fails. After much trial and error, I decided to give up on this manner of running SSIS packages. I decided to create the job as a CmdExec type job instead. When run manually, it woks fine, but when I schedule it, it doesn't run. Now, it's not that it gives an error message, it just DOESN'T run, it appears as "not scheduled" in the Job Activity Monitor, even though the schedule is listed in the Schedules property. How then, can I schedule the command to run? Thanks for the info.

The reason is very simple the local systems account does not have access to network resources anything you run through that account fails. xp_cmdshell is a better choice but you have to create a proxy to admin level account to run the Agent. All you need is covered below post again if you still need help, I know it works and can run for hours like clock work.

http://support.microsoft.com/kb/912911

|||

I usually run the Sql Server Agent under a service account that has the privilges required to run any processing.

You may run into another issue where errors in Analysis Services Commands are not reported when run via the Sql Server Agent. I am in the process of trying to find out if MS is aware of this issue. It appears to be an issue in all versions of SQL 2005 up to and including SP2.

sql

Packages, Jobs and Scheduling issues

I'm starting to have second thoughts about switching to SQL 2005.

After hours of trying, I finally managed to get SSIS packages to run as intended. Now, I need to create a schedule for the package. I create a job, owned by the Local System account, which SQL Server Agent also uses. I can run the Package myself, but when I schedule it, it always fails. After much trial and error, I decided to give up on this manner of running SSIS packages. I decided to create the job as a CmdExec type job instead. When run manually, it woks fine, but when I schedule it, it doesn't run. Now, it's not that it gives an error message, it just DOESN'T run, it appears as "not scheduled" in the Job Activity Monitor, even though the schedule is listed in the Schedules property. How then, can I schedule the command to run? Thanks for the info.

The reason is very simple the local systems account does not have access to network resources anything you run through that account fails. xp_cmdshell is a better choice but you have to create a proxy to admin level account to run the Agent. All you need is covered below post again if you still need help, I know it works and can run for hours like clock work.

http://support.microsoft.com/kb/912911

|||

I usually run the Sql Server Agent under a service account that has the privilges required to run any processing.

You may run into another issue where errors in Analysis Services Commands are not reported when run via the Sql Server Agent. I am in the process of trying to find out if MS is aware of this issue. It appears to be an issue in all versions of SQL 2005 up to and including SP2.

Packages error or hang in SQL Agent but not in BI IDE

Hi ,

I have an issues that worries me alot. I have SSIS packages scheduled in SQL Agent and they sometime error out or hang in Agent but never when i run them in BI IDE (Visual studio). There are two particular packages that always hang, alway in Agent but when i run them in BI IDE, they run just fine. Im was running them under in Agent under type= Sql server intergration services and also tried running under type = Operating System (CmdExec). Both types have the same problem. The packages hang. The log files dont say much, because the packages hang, no logging is doing so i dont get any infomation to determin what is going on. Can someone please help me ou.

Can you provide more information about the package:

What security settings are you using?

What tasks are in the package?

Where is the package hanging?

What log events do you have enabled?

Is it consistent? Happens everytime?

Does it happen if you run it under DTExec in the same credentials as under Agent?

Kirk Haselden
Author "SQL Server Integration Services"

|||The security settings of the package is EncryptSensitiveWithUserKey but no password
The task contained the the package are two data flow componenet ( in which there is a download from a remote source to local sql sever destination server ) , two execute sql task from which a sql statement is set to a varaible and assigned to the sql property of the datasources (using expression) in the data flow componenets and lastly , email task to send emails if the packaged failed or passed.
I have all the log even enable except for those in relation to the pipelines like OnPipelinePostEndOfRowset

It hangs everytime the packages runs in Sql Agent but not in VS (BI IDE)
I have only run it with dtexec under Agent and its still hangs.|||

This won't work because EncryptSensitiveWithUserKey uses the credentials of the user on the machine where the package is built.

Change the package protection level to "Rely on server storage for encryption".

Save the package to SQL Server and then schedule the package to run in Agent.

Should fix the issue.

Kirk Haselden
Author "SQL Server Integration Services"

|||is there any way i can do that without storing the packages in the db because i make changes to the pacakages very often and like it on the file system. If not, i can stomach storing it in the db.|||

Have you actually tried doing this on SQL Server 2005 SP1? BIDS will not let you save a package with 'Server Storage' ProtectionLevel. Therefore, I can't build a deployment manifest where I get the 'Rely on server storage for encryption' option. Just what do you mean by 'save the package to SQL Server'? I'm assuming that's using a deployment manifest file which opens the deployment wizard, is there a better way?

|||

DawnJ wrote:

Just what do you mean by 'save the package to SQL Server'? I'm assuming that's using a deployment manifest file which opens the deployment wizard, is there a better way?

this link describes how to save a package to sql server: http://msdn2.microsoft.com/en-us/library/ms137565.aspx

Packages error or hang in SQL Agent but not in BI IDE

Hi ,

I have an issues that worries me alot. I have SSIS packages scheduled in SQL Agent and they sometime error out or hang in Agent but never when i run them in BI IDE (Visual studio). There are two particular packages that always hang, alway in Agent but when i run them in BI IDE, they run just fine. Im was running them under in Agent under type= Sql server intergration services and also tried running under type = Operating System (CmdExec). Both types have the same problem. The packages hang. The log files dont say much, because the packages hang, no logging is doing so i dont get any infomation to determin what is going on. Can someone please help me ou.

Can you provide more information about the package:

What security settings are you using?

What tasks are in the package?

Where is the package hanging?

What log events do you have enabled?

Is it consistent? Happens everytime?

Does it happen if you run it under DTExec in the same credentials as under Agent?

Kirk Haselden
Author "SQL Server Integration Services"

|||The security settings of the package is EncryptSensitiveWithUserKey but no password
The task contained the the package are two data flow componenet ( in which there is a download from a remote source to local sql sever destination server ) , two execute sql task from which a sql statement is set to a varaible and assigned to the sql property of the datasources (using expression) in the data flow componenets and lastly , email task to send emails if the packaged failed or passed.
I have all the log even enable except for those in relation to the pipelines like OnPipelinePostEndOfRowset

It hangs everytime the packages runs in Sql Agent but not in VS (BI IDE)
I have only run it with dtexec under Agent and its still hangs.|||

This won't work because EncryptSensitiveWithUserKey uses the credentials of the user on the machine where the package is built.

Change the package protection level to "Rely on server storage for encryption".

Save the package to SQL Server and then schedule the package to run in Agent.

Should fix the issue.

Kirk Haselden
Author "SQL Server Integration Services"

|||is there any way i can do that without storing the packages in the db because i make changes to the pacakages very often and like it on the file system. If not, i can stomach storing it in the db.|||

Have you actually tried doing this on SQL Server 2005 SP1? BIDS will not let you save a package with 'Server Storage' ProtectionLevel. Therefore, I can't build a deployment manifest where I get the 'Rely on server storage for encryption' option. Just what do you mean by 'save the package to SQL Server'? I'm assuming that's using a deployment manifest file which opens the deployment wizard, is there a better way?

|||

DawnJ wrote:

Just what do you mean by 'save the package to SQL Server'? I'm assuming that's using a deployment manifest file which opens the deployment wizard, is there a better way?

this link describes how to save a package to sql server: http://msdn2.microsoft.com/en-us/library/ms137565.aspx

packages and data source views do not work?

Hi,

doing my first steps in SSIS I wanted to copy data from 2 different SQL 2000 database servers to a SQL 2005 Data warehouse. For not having to deploy additional views and procedures to the individual systems I chose to create a Data Source View to create an abstract view on the different data sources. I found out that I can have named queries pointing to the two different data sources in the same view.

1 Project, 2 Data Sources, 1 Data Source View with 3 Named Queries

When I now add a Data Flow Task to the Control Flow how can I specify my DSV as Source for Transformations? I even added both OLEDB Connections to the Connection Manager but the Named Queries from my DSV do not appear at all. I even tried "SELECT from [myNamedQueryFromDSV]" but without success.

The description available at http://msdn2.microsoft.com/en-us/ms403395.aspx is bullshit. There is nothing to expand about the "Connection Manager" in the Data Flow Window. I can add a OLEDB Source as described in the above HOWTO and double click it. But the dropdown field for Connection Manager does offer only the two OLEDB Connections and nothing more. Among the items of the access mode "Tables and Views" the named queries not appear. It does not even work with a homogeneous Data Source View.

How can I make it work? Ain't there a better (working) HOWTO out there on how to enable DSV als Data Flow Task data sources? Do I have to wait for SP2 to solve the problem or is it not possible by intention?

Cheers,
Frank

You say you "added both OLEDB Connections to the Connection Manager." I am not sure exactly what you mean, but I wonder if you right-clicked in the Connection Managers tray and added an OLEDB connection. That would not help you use a Data Source View.

As the splendidly written and accurate article at http://msdn2.microsoft.com/en-us/ms403395.aspx describes through its links, you should in fact add a Connection from a Data Source: http://msdn2.microsoft.com/en-us/ms403395.aspx.

You can tell if you have a Connection Manager that enables access to Data Source Views by looking at the icon. If it looks like a Data Source icon, you're in business. If it looks like an OLEDB Connection Manager icon, then you will not be able to see any Data Source Views.

Donald

|||

Hi Donald,

thanks for replying. Your hint about the Icon that should be different led me to the right actions.

But regarding the quality and accurateness of the article I have to disagree.

Please look add the following quote taken directly from the article:
"After a data source and its data sources views are in the Integration Services project, you add must add a connection manager that references the data source to the package. When you add the connection manager based on a data source to a package, Integration Services adds an OLE DB type connection manager. Only sources, transformations, and destinations that can use an OLE DB connection manager can use data source views."

It tells that an OLE DB Type connection manager is added and that DSVs only work with OLE DB connection managers. There's no real usable hint, that I need to create some-kind-of-reference connection. From reading the article I thought I need to add the OLE DB connection that I used already for the DSV. "New Connection from Datasource" as an option is not quite clear in its meaning. Where's the difference to the normal OLEDB Connection? It looks to be linking to that OLE DB connection underneath anyway.

And one problem remains: As my DSV does have Named Queries pointing to both OLE DB datasource connections (2x datasource 1, 1x datasource 2) I only see the 2 first named queries using the connection manager but I never see the 3rd from the other datasource regardless if I also create a "New Connection from Datasource" from the second one and specify that one for the OLE DB Source. So heterogeneous DSVs are not supported?

Best regards, Frank

|||

The doc is indeed accurate, although maybe I should not overpraise its style. :-) The following extracts do seem very clear to me and even include a link to detailed info on how to add the data source.

... a connection manager that references the data source

... add the connection manager based on a data source

Before you can use a data source view in a package, you must add the data source, on which the data source view is built, to the package. For more information, see How to: Add a Data Source Reference to a Package.

However, the reader is always right, and if you did not find the docs helpful, then that is fair enough. The information you needed was there, but you could not find it. It would be great if you could provide some detail on how you think they could have been better worded, or more clearly structured, to help us help others.

Meanwhile, you are indeed correct that you can only use one (the default) data source in a data source view with SSIS.

Donald

|||

Hello once again,

Donald Farmer wrote:

Before you can use a data source view in a package, you must add the data source, on which the data source view is built, to the package. For more information, see How to: Add a Data Source Reference to a Package.

This is exactly one of the misleading statements. I shall add the data source on which the DSV was built. And for the DSV I have added two OLE DB Data Sources to the project. So my conclusion would be to reuse them. When I right-click the connection manager of the package I can choose between several types of connections to be added. And as the article also states that DSVs only work with OLE DB connections my first choice would be to select "New OLE DB Connection ...". Doing so pops up a dialog with the OLE DB Data Sources I have already defined in the project and used for the DSV.

Maybe this would be a better explanation:

Before you can use a data source view in a package, you must add a data source reference ("New Connection from Datasource") to the project data source object, on which the data source view is built, to the package. Your project data source object should be an OLE DB data source. For more information, ...

And for the future it would be nice if the SQL 2005 Development team could allow something like "New Connection to Data Source View" which would be (imho) very user friendly.

As for the restriction that only the Named Queries / Table Queries basing on the default data source in a DSV can be used in packages - a hint in the documentation would be very appreciated - and surely not only by me. Is that a restriction by intention or is it a bug going to solved in the future?

I highly appreciate your dealing with this topic

Kind regards, Frank

|||I agree that the documentation is not clear. I'm still struggling trying to get this setup.

Package wont run in a scheduled job

Hello,

I have put together a simple SSIS package that runs fine both via BIDS and via Mgt Studio.

The problem I have is that I can't get a scheduled job to run it. My scheduled job has only 1 step - and that step runs the package.

When I right click in Mgt Studio (SQL Agent) and choose "Start Job" I get this error :

Executed as user: NMR044BRASQL886\SYSTEM. ....3033.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 14:10:56 Error: 2007-01-09 14:10:56.50 Code: 0xC0016016 Source: Description: Failed to decrypt protected XML node "DTS:Property" 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. End Error Error: 2007-01-09 14:10:57.04 Code: 0xC0202009 Source: B2B_Sales_Territory_Send_Back Connection manager "b2b_datamart" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E4D. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80040E4D Description: "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.". End Error Error: 2007-01-09 14:10:57.04 Code: 0xC020801C... The package execution fa... The step failed.

Can anyone help please?
I'm new to SSIS so clear+simple answers appreciated.

Thanks in advance
Mgale1

Check the ProtectionLevel proeprty of your package. My guess is that it is set to EncryptSensitiveWithUserKey. Is that correct?

This means that all passwords and such are encrypted with the username of the person that built the package hence when you run it as another user (which you are attempting to do here), it doesn't work. Read in BOL about the ProtectionLevel property to get a full understanding and then reply here with any problems.

-Jamie

|||

Hi Mgale1,

This could be due to the ProtectionLevel setting for the individual packages - that's my guess.

By default, these are set to EncryptSensitiveWithUserKey. This means as long as you personally execute the packages, your credentials are picked up and the packages execute in your security context. This is true even if you're connected to a remote machine, so long as you're using the same AD credentials you used when you built the packages. Does this make sense?

When the job you created executes, it runs under the SQL Agent Service logon credentials.

My understanding of the "Sensitive" in EncryptSensitiveWithUserKey is most likely flawed, but I cannot find a way to tell my SSIS package "hey, this isn't sensitive so don't encrypt it." Although this sometimes gets in the way I like this feature because it keeps me from doing something I would likely later regret. Anyway, my point is the Sensitive label is applied to connection strings and I cannot find a way to un-apply it (and I'm cool with that).

One of the first things an SSIS package tries to do (after validation) is load up configuration file data. This uses a connection, which (you guessed it) uses your first connection string. Since yours are encrypted with your own personal SID on the domain and this is different from the SID on the account running the SQL Agent Service, the job-executed packages cannot connect to the configuration files to decrypt them.

There are a couple proper long-term solutions but the one that makes the most sense is to use the EncryptSensitiveWithPassword Package ProtectionLevel option and supply a good strong password. You will need to supply the password when you set up the job step as well, and this should allow the package to run without needing your security credentials.

Note: You will also need this password to open the packages in BIDS (or Visual Studio) from now on... there's no free lunch.

Hope this helps,

Andy

|||

You may want to check this KB article:

An SSIS package does not run when you call the SSIS package from a SQL Server Agent job step

http://support.microsoft.com/kb/918760

|||

Admin,

This topic is a good candidate for a "sticky" for the forum.

This question is asked once a day... we (the forum) could come up with a synopsis on package encryption.

|||

I always use ProtectionLevel=DontSaveSensitive. Without exception.

This actually forces you to use configurations - and that is no bad thing in my opinion.

-Jamie

package variables passed to Execute SQL Task

Hi,

I am having trouble getting the Execute SQL Task to recognize the package-level variables in an SSIS package. The tasks fail execution. The package contains three Execute SQL tasks and none of them receive the variables. However, when I replace the '?' in the code with the variable values they execute fine. These are the steps I have taken thus far:

Made sure the three variables are package level.

Verified I'm using an OLE DB valid connection.

Verified the variables are properly mapped in the parameters mapping section of the task properties.

Tried all 4 types of variable to parameter mapping (even though '?' in the query and '0,1,2..' in the name should work).

Changed the Bypassprepare property to 'True'.

Breakpoints and watch windows to verify the variable values.

I actually tried the simple tutorials on a one-liner SQL statement that uses variables, but had the same problem. I know there must be something simple that I'm missing here, but any info would be greatly appreciated!

Thanks!

Also, the code for the first package that holds the parameter is:

DECLARE @.DatabaseName varchar(100)
SET @.DatabaseName = ?
DECLARE @.SPID smallint

And, of course, when I replace the ? with the DB name in the Value field of the variable it executes.

The Parameter Mapping has the following values:

User:atabasename Input Varchar 0

And the Variables set up has the following values:

databasename package string DB_NAME

Is there a way to see the query after it reads the variables to see if I have something syntactically incorrect in the values? Please let me know if any further info would help.. I'm really stumped on this since I get the same results with examples found everywhere else too.

Thanks!|||

You could use an alternative approach to built the SQL statement. Create a new variable, let's say SQLStatement; then use an expression (set EvaluateAsExpresion=True) in that variable to form the SQL statement. something like:

"Select * from " + @.[User::Variable1]

Then in the execute SQL task set SQLSourceType=Variable and select the variable name in SourceVariable drop-down list.

I like this approach because you an see easily how the sql statement is being built.

|||Thanks for the quick reply Rafael. I am unclear as to how this would resolve the issue. The package contains three lengthy Execute SQL tasks, and the package needs to be replicated for several different databases that are independently accessed in a development environment. My original idea was to use the package variable to simply change the DB name. Are you saying I should try changing each task to a SQLSourceType=Variable and use a SQLStatement-like variable that uses an expression for the code presently used in the Execute SQL task? If so, it's definitely worth a try, but I'm still unclear as to how I'm misusing the package variables and I'm sure I'll find future situations where they will be useful. Any ideas?

Thanks again!
|||

jheywood wrote:

Are you saying I should try changing each task to a SQLSourceType=Variable and use a SQLStatement-like variable that uses an expression for the code presently used in the Execute SQL task?
Thanks again!

Yes, that is my idea. That give you the benefit of testing the expression and check how exactly the sql statement is gong to be sent.

jheywood wrote:

but I'm still unclear as to how I'm misusing the package variables and I'm sure I'll find future situations where they will be useful. Any ideas?

Not sure..did you try changing the value of ByPassPrepare in the execute sql task?

|||Yes, I tried that too. I was hoping it was maybe just some stupid syntax problem that I might have overlooked with the parameter or the variable, but I guess not.

I'll try the other method since that definitely seems like a viable option. Thanks so much for the help!

Please let me know if you think of anything else about the var use, or need more information. I'm really stumped on it.
sql

Package Validation Error + Code 0xC004801C

Hi,

I am learning to use SSIS for creating packages. I am getting the following error when I try to execute my package...

Package Validation Error

Error at Data Flow Task[DTS. Pipeline]:The "runtime connection "FlatFileConnection "(122)" in the connection manager collection, Connections, of component "Flat File Destination ("120") does not have a value for the ID property. Verify that the ConnectionManagerID property of the runtime connection object has been set for the component.

Error at Data Flow Task [DTS.Pipeline]: component "Flat File Destination" (120) failed validation and returned error code 0xC004801C.

Error at Data Flow Task[DTS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

I'd appreciate if someone can help me with this. Do I need to change some settings? Also, what does the number in brackets indicate, say in FlatFileConnection(122)? Is it valuable information while debugging?

Just to give an idea, my package is like this -

source FlatFile -> Derived Column -> Lookup1 ->Lookup2 ->OLE DB destination(SQL server DB table)

Lookup1 and Lookup2 have error redirected to one flat file destination each.

Thanks in advance!

Geez....the error was just because I hadn't assigned a Connection Manager to that Flat File destination!! Oops!!!

Package Update and Build Process

My SSIS solution has about hundred packages and time to time I have to edit a package. I understand I could use 'Build' command to compile only updated package, as opposed to Rebuild which recomplies all of the packages.

Nevertheless, in both cases SSIS opens all of the packages in design environment before compilation. My packages are saved in SourceSafe and that process takes quite long and I was wondering if there was any other way to compile only updated package where none of the other packages are opened during Build/Rebuild process? For example we could use dtutil to deploy only updated packages without running Package Installation Wizard.

Turn the of the "Build deployment Utility" option ala http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=874332&SiteID=1. With this option disabled, each package will cease opening every time you debug just one package via F5 or select the build project or build solution menu items.

For that matter, turn off the Integration Services project "Build" option in Visual Studio's Configuration Manager. SSIS in BIDS doesn't compile/build anything, but rather, copies your hundred .dtsx files to the project relative "bin\" subdirectory. Its doubtful you need four copies of each of the hundred packages, one each in source control, and three each in your local workspace, two of which are superflous (e.g. those copies in bin\ and bin\Deployment)

As you mentioned, use dtutil, or xcopy for that matter (if appropriate) for deployment, rather than the Package Installation Wizard. For example see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1828408&SiteID=1, wherein dtutil is used for SQL server deployment.

|||

Thanks very much, your suggested approach would save me painful waiting time I had to endure before.

Asaf

sql

Package trasfer to remote server

I have developed my ssis packages on my local system.Now i want to move them to a remote server.I have connected to the remote server over vpn.Now how do i transfer them to the server.Once i transfer i need to make few changes to the connection.What is the best way to transfer and make it work.

And then i want to schedule the packages to run automatically.

Please let me know

My first suggestion would be to try to read some of the books on-line (BOL). However, barring that, you can transfer the packages in one of a few different ways depending on how it is that you would like to store them on the remote server.

1.

a) You can simply copy the package .dtsx files to a folder on the remote server (or any remote folder that the user account running the package has access to, more on that later).

b) If you are storing it on the msdb you would connect to your ssis server (follow the following link for more information on setting up the dcom correctly to allow you to connect http://sqljunkies.com/WebLog/knight_reign/archive/2006/01/05/17769.aspx) and then click through the stored packages -> msdb and create a new folder, then right click and press import package. The rest should be pretty self explanatory.

2. What type of changes do you need to make? If they are simply connection string changes and / or variable changes you can use an xml config file, the /set values tab of the job step maintenance screen or the data connections tab (for changes to the connections). I would suggest the xml config file option. If you need to open the package and edit, you can easily do this (provided you set the correct package level protection, i.e. encrypt w/ password or do not encrypt sensitive, etc) by simply opening the package from the remote server.

3. See the following threads / technet articles on setting up a job / credentials / proxy etc.

http://support.microsoft.com/kb/912911

http://support.microsoft.com/?kbid=918760

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1322146&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1955723&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=703968&SiteID=1

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2068280&SiteID=1

Let us know if you have any more questions or need any more help

Package templates

Greetings SSIS friends,

I am currently reading Ralph Kimball's "The Microsoft Data Warehouse Toolkit". In the book it mentions creating template packages to speed up the creation of future packages. In the book they also mention creating solutions and projects.

All sounds fair enough but I am confused a little.

Where should the template package be stored?! I created a Project and renamed the default package as "TemplatePackage". I setup some standard data sources and package variables. Do I now create a new package and place it in the same project? Or should my template package be deleted from the project?!

Hope my questions makes sense.

Package Templates
(http://wiki.sqlis.com/default.aspx/SQLISWiki/PackageTemplates.html)|||

That's brilliant.

Thanks a bunch!

sql

Package Synchronization?

What is the best strategy for maintaining package execution synchronization when using the SSIS API?

For example, I have discovered that it is quite possible to fire off several intances of the same package simultaneously.

Is a best practice perhaps to put the db in single user mode prior to kicking off the package and if so, how would you limit subsequent ASP.NET connectons, for example?

TIA,

Rick

Assert.True wrote:

What is the best strategy for maintaining package execution synchronization when using the SSIS API?

For example, I have discovered that it is quite possible to fire off several intances of the same package simultaneously.

Is a best practice perhaps to put the db in single user mode prior to kicking off the package and if so, how would you limit subsequent ASP.NET connectons, for example?

TIA,

Rick

Control access to the package so that it DOESN'T get fired simultaneously.

-Jamie

|||You can create Agent Job with your package. The Agent ensures only one instance of a particular Job is executed at the same time. Of course, it will be your responsibility to make sure the package is only executed via Agent (from ASP.NET - use sp_start_job SQL stored proc).

Package structure in SSIS

Hi

I have been working with DTC packages together with analysis service, and are

now begining on SSIS.

But I have some demands that's been giving me headache before, so I hope

there's a solution in the SSIS for the following.

My DTC packages was build to be executed i 5 steps/Packages

1. Transfeer data from my Oracle 9.2.0.4 to SQL 2000

2. Transform data

3. Quality check

4. Process dimension

5. Process cubes

BUT in 2005, I want a little more flexibilty. I want to execute smaller

packages, let's say, only my "general ledger".

This way I will be able to update some of my important cubes quicker. And

not wait until all data

is transferred.

So what I have done until now is that I have created SSIS packages per module. I

have a General_ledger.dtsx and a Sales.dtsx. Which I can execute separately.

I also have a MasterFlow.dtsx which contain an "Execute package Task"

for each package.

I can run the MasterFlow and it will run through all packages once every night.

Each package contains

1. Truncate package tabels

2. Transfer package

tabels

3. Execute dimensions

data

4. Execute cubes

5. Process

Dimensions

6. Process Cubes

Is this the best way?

I'm not satisfied

myself because,

when I run MasterFlow. I would like to execute all

"step 1" in all packages, before "step 2" and so forth.

Then I would be sure that all data is OK when all Cubes are processed, if I

have a virtual cube that contains data from 2 packages.

How would you design this kind of setup?

Thanx in adv.

Best Regards

Bjarne R Mikkelsen

A-TEX A/S

Why not have a third child package that processes the cubes? then you only have to execute the cube process upon successful completion of everything else.

Or, in each child package you could process the measure group (I'm assuming you are using AS2005) relevant to teh data that you have just transformed.

Your approach up to now seems pretty sound to me.

-Jamie

|||Thank you.

The processing of dimension and cubes are the least time consuming, so it would be a possibility, simply to arrange these in a 3. child package, which will be executed everythime a single package is run.

/Bjarne

Package still referencing old parameters from an old connection

I deleted and created a new OLE DB connection string then set all my connections to that string in my components in my SSIS package however below where it talks about EBN_TEMP1, that's an old database table that no longer exists after I unistalled and reinstalled SQL Server on this box. Why is it still refrencing old stuff? Is there some sort of refresh I have to do on my entire package due to the fact that I

1) Reinstalled SQL Server 2005

2) Deleted an old OLE DB Conenction my package was using (based on an old database that was in the previous SQL Server install) and createad a new OLE DB Connection in my package to point to a new database name after my reinstall of SQL Server 2005

TITLE: Package Validation Error

Package Validation Error

ADDITIONAL INFORMATION:

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 0" and "AccountNumber" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 1" and "Screen" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 2" and "CaseNumber" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 3" and "BKYChapter" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 4" and "FileDate" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 6" and "DispositionCode" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 23" and "BKUDA1" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 24" and "RMSADDR2" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 25" and "RMSCMPNAME_1" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 26" and "RMSADDR_1" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 27" and "RMSCITY_1" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 28" and "RMSSTATECD_1" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 29" and "RMSZIPCODE_1" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 30" and "RMSWORKPHN" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 31" and "BKYMEETDTE" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [EBN_TEMP1 [528]]: Columns "Column 34" and "RMSCMPNAME_2" cannot convert between unicode and non-unicode string data types.

Error at Data Flow Task [DTS.Pipeline]: "component "EBN_TEMP1" (528)" failed validation and returned validation status "VS_ISBROKEN".

Error at Data Flow Task [DTS.Pipeline]: One or more component failed validation.

Error at Data Flow Task: There were errors during task validation.

(Microsoft.DataTransformationServices.VsIntegration)

BUTTONS:

OK

I don't remember the format of error message, but this string (EBN_TEMP1) is probably the name of data flow component (source, destination, etc), not the connection name. Just look around the package to find an object with such name. Or double click the error message to get editor for this component.sql

PACKAGE START / PACKAGEEND In SSIS

This is a repeat listing - third time - of this problem.

Here's the deal:
If I turn on logging on an SSIS package in Development Studio, when the package executes it will log all the events I choose to the sysdtslog90 table in the MSDB database - INCLUDING the PACKAGESTART and PACKAGEEND events.

When I create my own custom logging, however, those two events ARE NOT being logged, even though I explicitly state in my script I want those two logged. Everything else in the script (OnWarning, OnPreExecute, OnPostExecute, etc.) is being logged.

In my reading, it states that the PACKAGESTART and PACKAGEEND events are defaults and are always logged and cannot be excluded.

If this is the case, can someone explain why they aren't getting logged?

I've seen other people have run across the same issue...This is largely due to technical issues within SSIS. In their rush to release SSIS with SQL Server 2005, Microsoft was unable to get the product fully assed. As a result, SSIS is half-assed at best, and portions of it are barely one quarter assed. You can add this glitch to a collection of SSIS inadequacies, including the ability to import XML but not the ability to export XML, and the ability to add headers to output files but not footers.|||I heard tale that SP2 supposedly clears this up? Is that what you've heard, or can I pretty much just hang it up?

Thanks!|||I haven't heard anything about SP2. Sure would be nice if they someday finished the application they rolled out.|||This is largely due to technical issues within SSIS. In their rush to release SSIS with SQL Server 2005, Microsoft was unable to get the product fully assed. As a result, SSIS is half-assed at best, and portions of it are barely one quarter assed. You can add this glitch to a collection of SSIS inadequacies, including the ability to import XML but not the ability to export XML, and the ability to add headers to output files but not footers.I didn't know that. Gives me a nice "after the fact" sense of smugness that I have so far been able to avoid using the pesky thing entirely.


I heard tale that SP2 supposedly clears this up? Is that what you've heard, or can I pretty much just hang it up?

Got any links? I would like to keep up even if I still come to the conclusion that it is more hassle than it is worth.|||Here ya go:

http://www.microsoft.com/downloads/details.aspx?FamilyId=d07219b2-1e23-49c8-8f0c-63fa18f26d3a&DisplayLang=en|||To any and all interested parties:

I eventually got a response from the Mothsership herself...see below:

I believe the reason the package in the linked thread was working after SP2 is that it was using an Execute Package task, while you are using a script task. The problems you're seeing don't seem to have anything to do with your custom logger - I was able to reproduce the issue using a simple package with a SQL Logger configured.

The PackageStart and PackageEnd events are special, in that they are always fired, regardless of filtering. However, it looks like executing a package through the script task stops the event from actually being propogated up. I'm unable to determine the cause right now, but I will log an internal bug for further investigation.

As a workaround, could you instead fire a custom event from inside the script task, right before you execute the child package?

This response came directly from someone at Microsoft...

The squeaky wheel does indeed get the grease! I guess we can keep our eyes hopefully peeled for a HotFix maybe...or at least getting this addressed in a future Service Pack.|||...I'm unable to determine the cause right now, but I will log an internal bug for further investigation. One step closer to ass-completeness.|||Man...you crack me up seriously!!!!

Kudos, Blindman ;)

Package setup, configurations and testing

Hi,

I'm looing for advice on how to organize my SSIS packages. I'll quickly describe my requiremenst, and then outline how I see the solution.

1. Should work for development in BI Studio. Design time experience should be good, so static schema information should be available in all steps. Packages should run in debug mode inside BI Studio.

2. Should be able to run automated tests (which I gather is difficult. See proposed solution below). Tests should run both on a test machine, in batch mode, and from BI Studio.

3. Should work for deployment.

The packages should be fine-grained. Automated tests will be implemented as SSIS packages that call the packages to be tested via an Execute Package Task. There will be one test package per testcase. A test will look like: [Create Schema] -> [Load test data]->[Execute Package to be tested]->[Verify expected results]->[Teardown].

There will be one master test package that executes all the tests.

For this I need a) some way to vary the connections for the packages depending on whether they are run from a testcase or from the normal control flow. I'm considering making the connection strings configurations based on a parent package variable. Other configuration types strike me as not suitable since developers will want to run packages both as part of normal flow and as testcases on their machines. Is this a viable strategy? Any drawbacks?

b) need to organize testcases some way. I tried putting tests in a subfolder of the project to minimize the clutter, but BI studio shows them at the root node. I tried putting a new project (for containing the tests) in the same folder, and adding the packages (via add existing item), but BI Studio insists of adding copies (!) of the package files, rather than reusing the existing files. Any suggestions?

I welcome any feedback, suggestions of alternate strategies, etc.

/Anders

AndersI wrote:

For this I need a) some way to vary the connections for the packages depending on whether they are run from a testcase or from the normal control flow. I'm considering making the connection strings configurations based on a parent package variable. Other configuration types strike me as not suitable since developers will want to run packages both as part of normal flow and as testcases on their machines. Is this a viable strategy? Any drawbacks?

One possible drawback is that when the developers run in BIDS (not the test case), the packages will use the connection strings coded in the packages. I like using configurations for my packages even in development.

Another approach might be to use another type of configuration for the packages in dev, but when you run them from the test pacakges, use Execute Process instead of Execute Package. You can call DTEXEC from Execute Process, and override the connection strings by using the /CONN switch, which should take precedence over the configurations.

AndersI wrote:

b) need to organize testcases some way. I tried putting tests in a subfolder of the project to minimize the clutter, but BI studio shows them at the root node. I tried putting a new project (for containing the tests) in the same folder, and adding the packages (via add existing item), but BI Studio insists of adding copies (!) of the package files, rather than reusing the existing files. Any suggestions?

Can't you just put the test case packages in one project, and leave the real ones in another project?

|||

Thanks!

I'm using configurations for the master packages to set the connection variables, and then passing them down to child packages via parent package variable configurations.

The reason I want the packages-to-be-tested in the same project as the testcase packages is so that you can run them in the BI studio debugger. If the package called via Execute Package is not in the same project, then the debugger won't display it. [Edit] Scratch that, it works. Must have been a user error on my part... [/Edit]

The Execute Package task uses a connection with an absolute path to refer to the called package, right? What is the recommended practice for handling this in a development team where developers may have the files checked out to different locations on their machines? I'm considering adding a "PackagePath" variable (also stored in the configuration file) to the master packages, and using expressions on the form @.[User:ackagePath] + "ChildPackage.dtsx" for the connectionstring on the file connection manager. Has anyone else tried this?

/Anders

|||

AndersI wrote:

The Execute Package task uses a connection with an absolute path to refer to the called package, right? What is the recommended practice for handling this in a development team where developers may have the files checked out to different locations on their machines? I'm considering adding a "PackagePath" variable (also stored in the configuration file) to the master packages, and using expressions on the form @.[User:ackagePath] + "ChildPackage.dtsx" for the connectionstring on the file connection manager. Has anyone else tried this?

Yes, I've done exactly that. It works well.

Package Runs in 1 minutes in Visual Studio but takes 5+ (sometimes hanges) as Job

I have an SSIS package that when run from Visual Studio takes 1 minute or less to complete. When I schedule this package to run as a SQL Server job it takes 5+ and sometimes hangs complaining about buffers.

The server is a 4 way 3ghz Xeon (dual core) with 8GB ram and this was the only package running.

When I look in the log I see that the package is running and processing data, although very very very very very slowly.

Has anyone else experienced this?

Thanks for your help

Marcus

You need to identify what the differences are between the two executions. It could be network latency, utilisation of the box by other processes, the user that you're running it as....anything.

What happens when you execute the package on the Xeon using dtexec.exe rather than thru SQL Server Agent. Do you get the same?

-Jamie

|||

I don't klnow what is going on with my system. The above package has ran find since I posted this message. I'm now having issues with stored procedure that is being ran from a job.

I can run the SP from Management Studio and it completes. The CPU does spike above 90% but it completes.

When I execute the same SP from job I get the following error and ALL connections to the database are killed.

Msg 64, Sev 16, State 1: TCP Provider: The specified network name is no longer available. [SQLSTATE 08S01]
Msg 64, Sev 16, State 1: Communication link failure [SQLSTATE 08S01]
Msg 10004, Sev 16, State 1: Communication link failure [SQLSTATE 08S01]

I have taken the SQL out of the SP and tried to execute it directly from within the job and it still fails wth the same issue.

I have no idea what is going on or where to start looking.

Here is a copy of the SP that I'm trying to run from a job.... Help... I'm glad it's a Friday

CREATE PROCEDURE [dbo].[sproc_LoadData_FactMessageOpen] AS

SET NOCOUNT ON
TRUNCATE TABLE [dbo].[FactMessageOpen]

INSERT INTO [dbo].[FactMessageOpen] (

[DateID],[TimeID],[MailingID],[ProfileID],[MessageFormatID],[TransactionDateTime])
SELECT
(SELECT DateID FROM dbo.DimDate WHERE DateAK = CAST(CONVERT(varchar(10),X.TransactionDateTime,101) AS smalldatetime)) AS DateID,
(SELECT TimeID FROM DimTime WHERE Hour12 = LTRIM(SUBSTRING(CONVERT(varchar(50),X.TransactionDateTime,100),CHARINDEX(':',CONVERT(varchar(50),X.TransactionDateTime,100))-2,2)) AND Minute = SUBSTRING(CONVERT(varchar(50),X.TransactionDateTime,100),CHARINDEX(':',CONVERT(varchar(50),X.TransactionDateTime,100))+1,2) AND NoonFlag = SUBSTRING(CONVERT(varchar(50),X.TransactionDateTime,100),CHARINDEX(':',CONVERT(varchar(50),X.TransactionDateTime,100))+3,2)) AS TimeID,
(SELECT MailingID FROM dbo.DimMailing WHERE MailingAK = X.MailingAK) AS MailingID,
(SELECT ProfileID FROM dbo.DimProfile WHERE ProfileAK = X.ProfileAK) AS ProfileID,
(SELECT MessageFormatID FROM dbo.DimMessageFormat WHERE MessageFormatAK IS NULL) AS MessageFormatID,
X.TransactionDateTime
FROM
(
SELECT
ER.TEMPLATE_ID AS MailingAK,
ER.EMAIL_ADDR_ID AS ProfileAK,
MIN(ER.ACTION_DT) AS TransactionDateTime
FROM
TEST.MDB.EMAIL_RESPONSE ER
JOIN
TEST.MDB.ACTION_SUB_ACTION_LOOKUP AL
ON
ER.ACT_SUB_ID = AL.ACT_SUB_ID
WHERE
ACTION_CD = 'G'
GROUP BY
ER.TEMPLATE_ID,
ER.EMAIL_ADDR_ID
) AS X
ORDER BY
DateID,
TimeID

|||Please share your connection strings... Could it be that the user running the job (the user assigned to the SQL Server service account) doesn't have access to the remote server/database?|||90% cpu utilization seems like too much. I'm getting the impression that you haven't examined the query execution in SQL Server Profiler. Correct?|||

Hi

I actually ran the query through SQL Profiler and it didn't come back with any recommendations. I alos looked at the explain plan and every node is using an index.

does the query look poorley written?

thanks

Package Name Sorting

SISS packages do not sort by name in BIDS and they do not sort by name when deployed to a SSIS server if choosing to store in sql server. The packages do sort by name when stored in file system. We have more than 100 packages on the server and just could not keep up with the locations. The only thing I can do is to create many folders. But even folders have no way to be sorted. Do I miss something here?

Try this: http://solidqualitylearning.com/blogs/erik/archive/2005/08/08/1269.aspx

Or this: http://www.sqldbatips.com/showarticle.asp?ID=78 (Jasper has updated it to make it work in BIDS as well as SSMS)

-Jamie

|||

Jamie,

Thanks for your reply. I tried both solutions in BIDS. The first one works, but it seems too much work. The second one would be a good choice if I could make it work.

When I try to sort the packages in a project folder called 'Test Packages', I continue to get the follow error message in the output window "Access to the path ....Visual Studio 2005\Projects\Integration Services Project\Test Packages\Test Packages.dtproj.old' is denied.' I understand the program is trying to create a new sorted dtpproj file to replace the old one. Do you know what I need to do to make this work? Since the projected is in the source safe, does that make the difference?

Thanks.

Jane

|||

Sorry, I've never used it. Try asking Jasper - I'm sure he won't mind helping out.

-Jamie

|||It is probably a read-only file, and perhaps caused by some form of source control. Jasper makes the source code available, so feel free to come up with a better method.