SSIS: Don't ask me why!

satya - 12/22/2019, 1:24:24 PM

what I installed?


SQL Server 2019 (1.5G of install)
Visual Studio 2019 (9G of install)

Install it on wifi! :)

satya - 12/22/2019, 1:52:00 PM

Strange things Visual Studio 2019 and SSIS

1. Documentation seem to indicate SSIS is installed along with visual studio 2019

2. However a "new project" doesn't show SSIS as a project option

3. It appears one has to use "extension manager" in vs to download again!!! wtf?? another 500MB of download on verizon bandwidth!!

4. So I did it anyway. it kicks me to browser to download it leaving aside the vs (wow! who designed this? Certainly a comity)

5. Then it seem to complain some strange processes are running for installation to proceed.

6. I closed vs. still issues. then i have to close "ie" the very browser that started this

7. It says it completed the setup!!!

9. wow!! or wtf!!

satya - 12/22/2019, 1:55:42 PM

Here are some of these vows explained

Here are some of these vows explained

satya - 12/22/2019, 2:38:40 PM

This is also discussed at length here on VS docs on Github

This is also discussed at length here on VS docs on Github

satya - 12/22/2019, 2:45:33 PM

SSIS Tech community page from MS

SSIS Tech community page from MS

You will see short and long postings on many aspects of SSIS

satya - 12/22/2019, 2:45:43 PM

ssis

Show images for: ssis

satya - 12/22/2019, 3:03:50 PM

Installing visual studio

Installing visual studio

satya - 12/22/2019, 3:07:27 PM

During install pick this to use SSIS later: Data storage and processing

satya - 12/22/2019, 3:12:26 PM

SSIS is primarily documented here: tutorials, lesson plans etc from MS

SSIS is primarily documented here: tutorials, lesson plans etc from MS

satya - 12/22/2019, 3:13:31 PM

SSIS VS 2019 Marketplace extension home page is here

SSIS VS 2019 Marketplace extension home page is here

satya - 12/22/2019, 3:14:35 PM

Tutorials are documented here

Tutorials are documented here

satya - 12/22/2019, 3:16:06 PM

In that tutorial creating a new ssis project is here

In that tutorial creating a new ssis project is here

satya - 12/22/2019, 3:43:31 PM

If you installed VS 2019, then the marketplace will show this

However I got this picture after "downloading" the extension. So you may see "download" against it as opposed to a green check box. It is not clear why it says download, even though the vs 2019 installed it using the data storage work loads

satya - 12/22/2019, 3:44:47 PM

Once this is installed as above, now a new project will show the right project type

satya - 12/22/2019, 4:23:27 PM

Directory structure of an SSIS project

Directory structure of an SSIS project

satya - 12/22/2019, 4:23:56 PM

it looks like this

satya - 1/12/2020, 11:34:13 AM

Creating first project

1. For a project template, search for "integration" as the key word. Don't use "ssis" for that is too narrow

2. There is an SSIS version that is for azure, so make sure you pick the non azure one if your goal is a plain ssis project template

3. vs will create a sub directory with the project name, so locate the parent directory or repo where you want to put this stuff.

4. You can see the details of the created artifacts on the right side in the solution explorer, which you can see above as well.

5. You can rename the created "package.dtsx" to a relevant name as you can add more packages to this project

satya - 1/12/2020, 12:14:30 PM

SSIS convert datetime from ISO format

SSIS convert datetime from ISO format

satya - 1/12/2020, 12:14:40 PM

SSIS convert datetime from ISO format

SSIS convert datetime from ISO format

Search for: SSIS convert datetime from ISO format

satya - 1/12/2020, 1:23:15 PM

Here is how you add an OLEDB connection manager on localhost to SSIS

Here is how you add an OLEDB connection manager on localhost to SSIS

satya - 1/12/2020, 2:10:50 PM

How can I add additional columns to an ssis input source?

How can I add additional columns to an ssis input source?

Search for: How can I add additional columns to an ssis input source?

satya - 1/12/2020, 2:18:38 PM

Adding derived columns

Adding derived columns

satya - 1/13/2020, 11:42:34 AM

How can I map the same input field to multiple output fields in SSIS

How can I map the same input field to multiple output fields in SSIS

Search for: How can I map the same input field to multiple output fields in SSIS

satya - 1/13/2020, 12:06:23 PM

SSIS mapping expression language examples

SSIS mapping expression language examples

Search for: SSIS mapping expression language examples

satya - 1/13/2020, 12:06:43 PM

How can I create a derived column from 2 columns in SSIS

How can I create a derived column from 2 columns in SSIS

Search for: How can I create a derived column from 2 columns in SSIS

satya - 1/13/2020, 12:12:33 PM

Script component is documented here

Script component is documented here

satya - 1/13/2020, 12:12:59 PM

Derived column transformation is here

Derived column transformation is here

satya - 1/13/2020, 12:22:06 PM

warning ssis: One or more columns do not have an alias assigned

warning ssis: One or more columns do not have an alias assigned

Search for: warning ssis: One or more columns do not have an alias assigned

satya - 1/13/2020, 12:24:42 PM

what are typecast functions in SSIS?

what are typecast functions in SSIS?

Search for: what are typecast functions in SSIS?

satya - 1/13/2020, 12:38:47 PM

Data Conversion conversation on SOF

Data Conversion conversation on SOF

satya - 1/13/2020, 1:29:15 PM

Essentials so far

1. From a csv file insert into an equivalent table that has an identity column

2. Flat file connection manager. Defines columns in the file and their data types. ISO date time stamp points to DT_Date type

3. OLE DB connection manager. for the database.

4. Flat file source reading from the file.

5. Columns are not sufficient to populate the table as the table has additional columns

6. Need a derived column transformation. You cannot map one column to multiple columns. So you have to have each column listed here, even though they are duplicate data but with different column names. ex: last_updated_by, created_by where both point to the logged in user.

7. system variables like the logged in user @[System::CreatorName] can be used as values to the derived columns

8. Finally the OLEDB destination with a table or view. Here the mappings have to be one to one. (see above). There is a strange behavior with keep identity. when you want the identity to be automatically updated, turn off this flag and use "ignore" as the input to this identity column.

satya - 1/14/2020, 11:44:22 AM

Execute SQL Task ssis

Execute SQL Task ssis

Search for: Execute SQL Task ssis

satya - 1/14/2020, 11:44:45 AM

Docs on execute sql task

Docs on execute sql task

satya - 1/14/2020, 11:46:49 AM

Script Task is documented here

Script Task is documented here

satya - 1/14/2020, 11:46:57 AM

Script Task in SSIS

Script Task in SSIS

Search for: Script Task in SSIS

satya - 1/14/2020, 11:49:51 AM

Script Component in SSIS

Script Component in SSIS

Search for: Script Component in SSIS

satya - 1/14/2020, 11:50:01 AM

Script Component is documented here

Script Component is documented here

satya - 1/14/2020, 11:50:19 AM

Using a stored procedure to ingest data into SQL Server using SSIS

Using a stored procedure to ingest data into SQL Server using SSIS

Search for: Using a stored procedure to ingest data into SQL Server using SSIS

satya - 1/14/2020, 11:53:09 AM

Using a stored procedure to ingest data into SQL Server using SSIS

1. Folks may use OLE DB Command to execute a stored proc for each row

2. This will be expensive as that many stored procs will be called

3. Suggested solutions include using a staging table and run the procs in side the database for each row

4. An alternative to 3 is to use a similar approach using c# script code and Data Table object of .net to call the stored proc with the table as an input

satya - 1/14/2020, 11:53:27 AM

is there a bulk stored proc call in SSIS?

is there a bulk stored proc call in SSIS?

Search for: is there a bulk stored proc call in SSIS?

satya - 1/14/2020, 12:00:02 PM

Here are some notes on bulk inserts

Here are some notes on bulk inserts

satya - 1/14/2020, 1:40:45 PM

How to store objects with rules in a relational database: an ETL question

How to store objects with rules in a relational database: an ETL question

Search for: How to store objects with rules in a relational database: an ETL question

satya - 1/14/2020, 1:45:04 PM

How to store objects with rules in a relational database: an ETL question

1. Entities in a relational database are actually collection of objects that are related

2. when they are individually constructed by transactional systems stored procedures can construct one at a time with all the rules applied and foreign keys, parent keys, and child keys satisfied.

3. what do you do when you try to load a million of them?? Calling stored procs one at a takes too long

4. How do you flatten the objects? where do you apply the business rules? In the database or outside the database?

5. i wonder, if the solution is to apply them in a layer prior to the target database where 1) first the rules are applied on an object by object basis in parallel 2) then splice the objects into multiple streams where each stream is fully constructed accurate table. 3) Then load them using bulk. Just wonder!!!

satya - 1/14/2020, 1:46:19 PM

what is the difference between command and a task in ssis

what is the difference between command and a task in ssis

Search for: what is the difference between command and a task in ssis

satya - 1/29/2020, 5:56:36 PM

How do I pass variables between ssis tasks?

How do I pass variables between ssis tasks?

Search for: How do I pass variables between ssis tasks?

satya - 1/29/2020, 5:56:59 PM

Perhaps there is something in this article

Perhaps there is something in this article

satya - 1/29/2020, 6:05:21 PM

Few notes

1. variables have scope

2. at the package level or task level

3. right click on task to set a break point

4. tasks can define variables which can be set against parameters

satya - 1/29/2020, 6:06:33 PM

user scope in ssis variables

user scope in ssis variables

Search for: user scope in ssis variables

satya - 1/29/2020, 6:08:05 PM

Basics of variables in SSIS

Basics of variables in SSIS

satya - 1/29/2020, 6:09:07 PM

what is user:: in ssis variables

what is user:: in ssis variables

Search for: what is user:: in ssis variables

satya - 1/29/2020, 6:11:13 PM

Microsoft docs on variables

Microsoft docs on variables

satya - 1/29/2020, 6:16:23 PM

Notes

1. variables can be system defined or user defined

2. users can define variables at a package, task, container etc.

satya - 1/29/2020, 6:17:44 PM

On a variable

1. name

2. namespace(This is where I have doubts on)

3. whether it can raise on event on value change

4. can be an expression

satya - 1/29/2020, 6:18:46 PM

And there it is, the namespace discussion

Integration Services provides two namespaces, User and System. By default, custom variables are in the User namespace, and system variables are in the System namespace. You can create additional namespaces for user-defined variables and change the name of the User namespace, but you cannot change the name of the System namespace, add variables to the System namespace, or assign system variables to a different namespace.

satya - 1/29/2020, 6:19:03 PM

Only 2 namespaces: user and system

Only 2 namespaces: user and system

satya - 1/29/2020, 6:20:19 PM

Oh, sorry, looks like you can define additional namespaces if you like

Oh, sorry, looks like you can define additional namespaces if you like

satya - 1/29/2020, 6:30:39 PM

ftp task is documented here at MS

ftp task is documented here at MS

satya - 1/29/2020, 6:41:40 PM

How do I list the files that an ftp task just received in ssis?

How do I list the files that an ftp task just received in ssis?

Search for: How do I list the files that an ftp task just received in ssis?

satya - 1/29/2020, 7:07:12 PM

Basics of foreach container

Basics of foreach container

satya - 1/29/2020, 7:11:49 PM

A set of articles on SSIS from Redgate

A set of articles on SSIS

satya - 1/29/2020, 10:33:12 PM

Advanced expressions in ssis

Advanced expressions in ssis

satya - 1/29/2020, 10:40:53 PM

FileSystem task docs

FileSystem task docs